How do I create a countifs formula if I have a column with multi drop down?
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.
Best Answer

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?
Answers

I think you can use CONTAINS to do that.

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

Sure thing. In this formula I have a column called "multi" that I have a multiple dropdown 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 mutliselect 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?

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!

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?

Thanks! It works!!

Awesome! Glad you got it going.

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!
Help Article Resources
Categories
Check out the Formula Handbook template!