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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!