Countifs
Hi,
I have been searching the boards and am not coming up with what I want so I am hoping someone can point me in the right direction.
I have a sheet with a multi-select column. In the column you can select:
A
B
A, B
I want to find the 3 formulas that will count the number of times A shows up by itself, the number of times B shows up by itself, and the number of times that A, B show up together. Every time I try it keeps counting all of the times, for example, A shows up whether it be by itself or with another.
Thank you so much!
Best Answer
-
Can you share the exact formula you're using? Mine works fine, so you likely either have a syntax error or a data issue.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@wthalstead Try these. Replace "Column:Column" with the column name range you're evaluating.
=COUNTIF(Column:Column, "A")
=COUNTIF(Column:Column, "B")
=COUNTIF(Column:Column, AND(HAS(@cell, "A"), HAS(@cell, "B")))
When it comes to multi-select cells, looking for a single value will only match if the entire cell matches that value. Using HAS allows the formula to look for specific whole values in a multi-select cell. For example, if the cell has values of Hello and Goodbye, looking for a cell equal to "Hello" will not match that cell, but looking for one that HAS(@cell, "Hello") will match.
Just a note - it's always helpful to include the formulas you have tried so far.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff. The first two worked great, but the third came back as #UNPARSEABLE.
-
Can you share the exact formula you're using? Mine works fine, so you likely either have a syntax error or a data issue.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I am all set. You were right - it was a syntax. I had the @Cell capitalized so it didn't like it. I really appreciate your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!