Counting specific values in multi select cells

TJordan

Hello!

I've read through several different posts about which formula to use to count specific text values in a multi-select cell however, I'm still getting errors on my end. I'm trying to get a count for each virtual event my team has selected. The formulas I have tried are:

1. =COUNTIF([Virtual Event]:[Virtual Event], CONTAINS("Margarita Making Class", [Virtual Event]:[Virtual Event])) - results return '0'
2. =COUNTIF([Virtual Event]:[Virtual Event], CONTAINS("Margarita Making Class")) - result returns INVALID OPERATION
3. =COUNTIF([Virtual Event]:[Virtual Event], HAS("Margarita Making Class")) - result returns INVALID OPERATION

Not sure what I'm doing wrong? Maybe formatting?

Nick Korna

Hi @TJordan,

HAS will work, but you need to make a slight change to add an @cell reference:

=COUNTIF([Virtual Event]:[Virtual Event], HAS(@cell, "Margarita Making Class"))

Hope this helps, but if you've any problems/questions, let us know!

TJordan

Hi @Nick Korna !

Thanks for helping me on this. The formula I want to use needs to be a column reference vs. @row. I tried this and the results still came out as 0 or INVALID OPERATION.

=COUNTIF([Virtual Event]:[Virtual Event], CONTAINS([Virtual Event]:[Virtual Event], "Margarita Making Class"))

=COUNTIF([Virtual Event]:[Virtual Event], HAS([Virtual Event]:[Virtual Event], "Margarita Making Class"))

Here's an example of the sheet.

Nick Korna

The @ cell is checking if the text appears in a given cell - the range is still the whole column.

If you're wanting a check for the row (which will end up as a 1 if present/0 if not) then you would use:

=COUNTIF([Virtual Event]@row, HAS(@cell, "Margarita Making Class"))

You can do some extra things with this if necessary (e.g. folding it into an IF statement to give something other than a binary response), but I am not sure what your desired end outcome is.

TJordan

Gotcha! I'm hoping I can use this formula on the sheet summary to get a count for each of the virtual event options:

-Office Feud (Have your team compete against the other ProServ teams)

-Margarita Making Class

-Office Mix and Mingle

-Haunted House (Virtual Escape)

-Do Good Dash (Online Charity- based team building)

-Tiny Campfire (Ghost Stories & Smores)

-Other

-Not Interested