Countif a name from a list of multiple names

SSParks

Greetings-

I need formula help to count a name EMMA that is selected from a drop-down that has other names selected as well. Basically I need to county if Emma's name is listed in the Education Naturalist column that has other names included in the same cell.

If I use =COUNTIF({Program Survey Range 1}, "Emma") it will only pull any cells that only has EMMA listed wo any other names.

Jennifer Kurtz

Hi @SSParks -

When you have a multi-select drop down, you can use the "HAS" function to search for all instances of a selection. In your formula, you could add HAS to your criteria like this:

 =COUNTIF({Program Survey Range 1}, HAS(@cell, "Emma"))

Hope that helps! 

SSParks

thank you for this information when I apply this to a cell that's coming back with 2 showing her name but I'm actually seeing three when I look at the column. Are we missing something?

SSParks

https://community.smartsheet.com/discussion/comment/462630#Comment_462630

thank you for the information when I apply that formula. It's coming back with a number two as a result but visually I'm seeing her name listed three times am I missing something?

Jennifer Kurtz

@SSParks - Ah - yes! It looks like the cell reading "Margot, Emma, Da…" was typed in manually, without using the drop down selection in the column.

It would probably be a good idea to restrict that column to "list values only" (within the Column Properties) so that users are entering the information in the same way every time, and so that your formula will work consistently.

(I can't think of a way you could successfully count instances of names in that column if they're being selected from a drop down at times, but entered manually at other times.)

Does that help?