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

Tags:

• Employee

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

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

• Employee

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

October 8 - 10, Seattle, WA | Register now

• Employee

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.

• Employee
edited 10/13/23

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

• Employee

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.

• Employee
edited 10/13/23

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.

• Employee

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