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

@Genevieve P. thank you so much! This work flawlessly.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.9K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!