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.
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!