How do I create a countifs formula if I have a column with multi drop down?

Joy Kwan

How do I create a countifs formula if I have a column with multi drop down and I need to specify which word to include for my count count? I would like to use the HAS function but it is not working as I am also referencing the data from another sheet so I can not use @cell with the HAS.

David Tutwiler

Ok, shouldn't be a problem. With CountIFS you will need to make sure that you define your second range and the criteria for it to match. I'll use my same formula, but add a column called type and look for that cell to contain the letters "DNA". Rather than returning a 2 it returns a 1 as only one of the cells has both a 2 and the letters "DNA".

=COUNTIFS(multi:multi, CONTAINS("2", @cell), type:type, "DNA")

If this still isn't working for you, can you provide the formula you're using?

David Tutwiler

I think you can use CONTAINS to do that.

Joy Kwan

It is coming back with Zero. Would you be able to show how I would write the formula?

David Tutwiler

Sure thing. In this formula I have a column called "multi" that I have a multiple drop-down selector with 1,2,3,4,5 available to pick. I populated the first cell with 1,2,3 and the second with 2, and the rest with other numbers that aren't 2. I'm looking for 2 and I expect it to return 2 instances since it is in the first mutli-select and the second.

=COUNTIF(multi:multi, CONTAINS("2", @cell))

So this looks at the whole range of the column (multi:multi) and checks each cell (@cell) on whether or not it contains a 2.

Would that work for what you're looking for?

Joy Kwan

I would like to use the COUNTIFS as I have multiple criteria. The formula works for 1 criteria but when I add another criteria it returns a Zero. Thank you!

David Tutwiler

Ok, shouldn't be a problem. With CountIFS you will need to make sure that you define your second range and the criteria for it to match. I'll use my same formula, but add a column called type and look for that cell to contain the letters "DNA". Rather than returning a 2 it returns a 1 as only one of the cells has both a 2 and the letters "DNA".

=COUNTIFS(multi:multi, CONTAINS("2", @cell), type:type, "DNA")

If this still isn't working for you, can you provide the formula you're using?

Joy Kwan

Thanks! It works!!

David Tutwiler

Awesome! Glad you got it going.

Sharon I

Hi David, I have spent an age on this exact same problem and your answer has solved it for me! Thank you, you are amazing!