google-sheetsdropdown

SUMIF from Drop Downs


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.net/mLGV2pGD.png


Solution

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