CountIF Has Multi Select Count
Hi all - I know this question has been asked many times and I've tried all of those solutions, but my output keeps showing up as zero…. I'm going to provide super specifics as I'm not sure if it's an issue with how my column is configured - I can't seem to figure it out!
Column "Goals" is a dropdown list with 5 options and multi-select (restricted to list values only). The selections are a little bit wordy, so not sure if this would factor.
I need to count every time a certain option appears in the column (obviously including the ones that have multiple answers)
Current formula I'm using based on other answers in this forum:
=COUNTIF({goals}, HAS({cellname}, "Doesn't Matter, Get Better"))
goals = entire column
cellname = I clicked on one cell within the column "goals"
I saw other answers referencing @cell. I was not entirely sure what this meant so I assumed its clicking on an individual cell within the column I want to search for.
I can confirm using a standard COUNTIF formula works great, it obviously just doesn't count the ones that have multiple answers.
HELP PLEASE! :)
Best Answers
-
Hi @ajk1317,
The @cell is what you would want to use here:
=COUNTIF({goals}, HAS(@cell, "Doesn't Matter, Get Better"))
This would count the cells in the Goals column where a cell has "Doesn't Matter, Get Better" as a selected option.
Hope this helps, but if you have any problems/questions then let us know!
-
@ajk1317, here you would switch from using COUNTIF to using COUNTIFS as you have multiple criteria. The formula would be very similar, just with an added S (bolded):
=COUNTIFS({goals}, HAS(@cell, "Doesn't Matter, Get Better"), {projectstate}, "Active")
Answers
-
Hi @ajk1317,
The @cell is what you would want to use here:
=COUNTIF({goals}, HAS(@cell, "Doesn't Matter, Get Better"))
This would count the cells in the Goals column where a cell has "Doesn't Matter, Get Better" as a selected option.
Hope this helps, but if you have any problems/questions then let us know!
-
@Nick Korna This was super helpful and worked. I didn't know it was literally type "@cell". Thank you!
Would you mind helping one more function into the same formula so I add a perimeter. I need the same function to weed out the options that only show "Active" as an option in a different column.
Formula I have it as is:
=COUNTIF({goals}, HAS(@cell, "Doesn't Matter, Get Better"), {projectstate}, "Active")
Hoping my question makes sense. Not sure if I have to add an "And" function to this? Thank you in advance
-
@ajk1317, here you would switch from using COUNTIF to using COUNTIFS as you have multiple criteria. The formula would be very similar, just with an added S (bolded):
=COUNTIFS({goals}, HAS(@cell, "Doesn't Matter, Get Better"), {projectstate}, "Active")
-
Much appreciated! Thank you @Nick Korna
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!