I'm trying to count the total for the number of instances if it's Late, Undertime, etc. from a drop down on multiple cells.

Unfortunately, nothing is working and I've been searching for a solution for 2 hours now.

It's only showing errors whenever I try to tweak the formula.

.png

I'm trying to count the total for the number of instances if it's Late, Undertime, etc. from a drop down on multiple cells.

Unfortunately, nothing is working and I've been searching for a solution for 2 hours now.

It's only showing errors whenever I try to tweak the formula.

https://i.sstatic/mLGV2pGD.png

• google-sheets
• dropdown

Share Improve this question Follow edited Mar 25 at 2:49 Gerald G asked Mar 25 at 2:48 Gerald GGerald G 322 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

It appears that using COUNTIFS that way checks if the range is equal to "Late", "Absent", "Swap RD", and "Undertime" all at the same time, which cannot be true.

Based on Google's Documentation, COUNTIFS's multiple criteria has a 1:1 mapping for each range (you do not reuse the same ranges).

To count the total number of instances, you can use this instead:

COMMAND: =SUM(COUNTIF(B1:E1, "Option 1"), COUNTIF(B1:E1, "Option 2"))

Since we know COUNTIF(RANGE, VALUE_TO_MATCH) will return the total count for 1 condition (in this example if it matches "Option 1"), we can use multiple COUNTIF with the same range for different conditions alongside with SUM to get the total number that you are looking for.

Below is a table that uses COMMAND

A	B	C	D	E
4	Option 1	Option 2	Option 1	Option 1

Here's an image version of the table

Hope this helps!

You can try this alternative formula with TextJoin and regexmatch for checking the small formatting issues such as handling extra spaces and matches the value inside the array from each target cell and used the IF function to return all the values in the different criteria that comes TRUE and using SUM to count the final number of occurrences.

Formula used

=SUM(ARRAYFORMULA(IF(REGEXMATCH(E2:I2, TEXTJOIN("|", TRUE, {"Late","Absent","Swap RD","Undertime"})), 1, 0)))

As you are trying to countif a number of different criteria are true, you need to use arrayformula and array literals:

=arrayformula(sum(countif(E3:I3,{"Late";"Absent";"Swap RD";"Undertime"})))

N.B. as you are counting over a horizontal range, the criteria array must be vertical to achieve the desired result, hence the semi-colons in the array literal.