Extract data on drop-down list
I have a drop-down list with names, and am trying to get a count of items (rows) against each name in the drop-down list. That would be as simple as =countifs, HOWEVER, I'm stuck in that that there can be more than one name in the cell of a specific row, and in this case the formula doesn't recognize any individual name in that "group" of names in that specific row. Any advice on how to solve that? Thanks!
Best Answers
-
@Hermien Venter This from @Mark Cronk
There are 2 unique functions in Smartsheet to deal with multiple select fields; HAS and COUNTM.
COUNTM: Counts the number of elements in a multi-contact or multi-select dropdown column cell or cell range. Returns the total number of elements found. https://help.smartsheet.com/function/countm
HAS: Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.https://help.smartsheet.com/function/has
Check those out.
- countif(HAS()) is probably what you need ...
dm
-
In that case you will need to work in a FIND function.
=COUNTIFS({Range}, FIND("John Doe", @cell) > 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
@Hermien Venter This from @Mark Cronk
There are 2 unique functions in Smartsheet to deal with multiple select fields; HAS and COUNTM.
COUNTM: Counts the number of elements in a multi-contact or multi-select dropdown column cell or cell range. Returns the total number of elements found. https://help.smartsheet.com/function/countm
HAS: Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.https://help.smartsheet.com/function/has
Check those out.
- countif(HAS()) is probably what you need ...
dm
-
Are you using a contact type column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks @DMurphy , Glad you found value in one of my previous posts. Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Paul, yes, it's a contact type column
-
In that case you will need to work in a FIND function.
=COUNTIFS({Range}, FIND("John Doe", @cell) > 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
DMurphy, I found your fomula doesn't work if there are at times multiple names in a cell, however, Paul Newcome's solution overcomes that.
Thanks a ton, I appreciate this!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!