Countif a name from a list of multiple names
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.
Best Answer
-
@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!
Answers
-
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!
-
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?
-
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?
-
@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?
-
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.
-
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?
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!