Formula help - count of a value from a multi-select dropdown field

Ann Marie

I have a column in a sheet that is a multi select drop down containing people's names. I then want to count the number of cells that contain a particular name - whether it's the only name listed or is one of the names in the cell. Can someone help me with what formula to use?

This formula is only counting the cells that have this person's name in it solely (i.e. doesn't have another name selected in the drop down): =COUNTIF({Wave 2 Tester}, "Ricky Jones"). I expected that result.

I just can't figure out how to write the formula to count cells where "Ricky Jones" is listed whether solely or one of multiple names in that cell. I tried this formula: =COUNTIF({Wave 2 Tester}, "*Ricky Jones*") but that didn't work and actually resulted in 0 (so not even getting ones where's he's listed by himself). Any ideas? Thanks!

Paul Newcome

Try this:

=COUNTIFS({Wave 2 Tester}, HAS(@cell, "Ricky Jones"))

Paul Newcome

Try this:

=COUNTIFS({Wave 2 Tester}, HAS(@cell, "Ricky Jones"))

Ann Marie

Thanks Paul - that didn't work perfectly (as in I couldn't get it to perform the HAS function and count in the same formula) BUT I used your suggestion to create a new column to do the HAS evaluation and then a separate formula to count where it was true. Thank you!

Paul Newcome

Were you getting an error or an incorrect count? I use the above fairly regularly with no issue.

Ann Marie

Actually, Paul - I got it to work. I had done something wrong with the reference. Oops! Thanks for the advice!

Paul Newcome

Happy to help. 👍️

Ciprian Borzasi

Worked for me as well.

Cheers.