How do I create an CountIF statement to include multiple selections within a cell?
I'm creating sheet summary calculations to tally the total count of a specific dropdown menu item; however, I am using multiselect. I know how to use a standard COUNTIF formula, but it only returns the total count if the option is the only one selected. Here is the formula I'm using:
=COUNTIF([Level III Request]:[Level III Request], "Solution Video")
The option for Solution Video appears six times but only three times by itself, so the formula returns a total count of 3. What logic is required to count the option when bundled with other selections? Below is a screenshot of my sheet. Any help is greatly appreciated!
Best Answers

Try using a CONTAINS() statement in your criteria for the COUNTIF:
=COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))

Hey @Jeannette Leary
Can you post the formulas you've tried?
In any of your formulas (e.g. COUNTIFS) you'll need to make sure you're also using HAS to see if the cell has that value selected (versus = that exact value without other selections).
For example:
=COUNTIFS({column range}, HAS(@cell, "Value"))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

Try using a CONTAINS() statement in your criteria for the COUNTIF:
=COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))

Thank you very much @gstotts !

@gstotts sorry, one last question  can I add an OR function to have the formula search for multiple criteria? I tried adding OR to the formula you shared but I'm receiving an error message.

I think this should work but haven't had a chance to test myself.
Example: to return a count if the Solution video or In Person Event is contained in the field:
=COUNTIF([Level III Request]:[Level III Request], OR(CONTAINS("Solution Video", @cell), CONTAINS("In Person Event", @cell))
OR Documentation: https://help.smartsheet.com/function/or

OK, I tried that and it returns a count, but it's incorrect (it lists 1 when it should be 2 since both options are listed in the sheet).

Ok. Above would only work for counting it once if either of those are present  not counting how many of them are present in each. I'd have to think about that one a bit.

You could just count them individually and add them together I suppose:
=COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))+COUNTIF([Level III Request]:[Level III Request], CONTAINS("In Person Event", @cell))
or (written with sum instead of +):
=SUM(COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell)), COUNTIF([Level III Request]:[Level III Request], CONTAINS("In Person Event", @cell)))

@gstotts thanks, but these aren't changing the totals for some reason (it still reflects 1 when both criteria exist).

Hello all, I am trying all of the formulas above (and others) but I am still struggling to get a final count of each Source category (covid, lessons learned, project team, etc.). As you can see on the left, the IF, SUMIF, COUNTIF, SUMIFS, and COUNTIFS formulas are not counting correctly (column 2). See Column 3 which the actual values. Thank you in advanced.

Hey @Jeannette Leary
Can you post the formulas you've tried?
In any of your formulas (e.g. COUNTIFS) you'll need to make sure you're also using HAS to see if the cell has that value selected (versus = that exact value without other selections).
For example:
=COUNTIFS({column range}, HAS(@cell, "Value"))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. thank you so much! This work flawlessly.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!