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

@SSParks - Thanks for the kind words! I love the Smartsheet Community, too — it's so helpful to have another set of eyes on something that gets me stuck or to have help solving a problem. :)

And I'm still learning about post etiquette, so I'm not a good person to ask (haha). If you tag someone, I think they're notified of a response. The quote option may be especially helpful when there are multiple responses in the string?

Have a great day!

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?

SSParks

WOW - good catch! This is why I love people like you on this forum! I appreciate the help in the formula is working wonderfully. Thank you again.

SSParks

just out of curiosity when I respond to a user like yourself, do I need to hit the quote option at the bottom of what you typed in ordered for you to be notified of my response or can I just respond to the actual question I posted?

Jennifer Kurtz

@SSParks - Thanks for the kind words! I love the Smartsheet Community, too — it's so helpful to have another set of eyes on something that gets me stuck or to have help solving a problem. :)

And I'm still learning about post etiquette, so I'm not a good person to ask (haha). If you tag someone, I think they're notified of a response. The quote option may be especially helpful when there are multiple responses in the string?

Have a great day!