How do I create an CountIF statement to include multiple selections within a cell?

Tony Fronza

I'm creating sheet summary calculations to tally the total count of a specific dropdown menu item; however, I am using multi-select. 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!

gstotts

Try using a CONTAINS() statement in your criteria for the COUNTIF:

=COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))

Genevieve P.

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

gstotts

Try using a CONTAINS() statement in your criteria for the COUNTIF:

=COUNTIF([Level III Request]:[Level III Request], CONTAINS("Solution Video", @cell))

Tony Fronza

Thank you very much @gstotts !

Tony Fronza

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

gstotts

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

Tony Fronza

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

gstotts

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.

gstotts

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

Tony Fronza

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

Jeannette Leary

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.

Genevieve P.

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

Jeannette Leary

@Genevieve P. thank you so much! This work flawlessly.