COUNTIF with HAS Formula in Sheet Summary
I am trying to do a COUNTIF in the Sheet Summary for a column that is a Multi Select Dropdown. I know that you are supposed to use the HAS function, but I can only seem to get that to work in the sheet with @Cell. Any suggestions?
=COUNTIF([Department of Concern]:[Department of Concern], HAS([Department of Concern]:[Department of Concern], "PCC"))
The formula gives me an answer of 0 but there are several cells that have PCC selected.
Best Answer
-
Are you receiving a specific error when you use @cell? If so, is it possible that the syntax isn't quite right?
@cell will need to be typed fully in lower-case in order for it to be recognized, but it should work in a Sheet Summary Field:
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Try =COUNTIF([Department of Concern]:[Department of Concern], "PCC") without the has.
-
I tried that, but it only counts the cells that ONLY have "PCC" selected. If there is more than one choice selected, it doesn't show in the total. I should have 12 but I get 9 using that formula.
-
You're right, you will need to use @cell in your HAS function to indicate that it should look into each of the cells in the previously stated range.
Try this:
=COUNTIF([Department of Concern]:[Department of Concern], HAS(@cell, "PCC"))
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
The problem is that @cell doesn't work in the Sheet Summary tab.
-
Are you receiving a specific error when you use @cell? If so, is it possible that the syntax isn't quite right?
@cell will need to be typed fully in lower-case in order for it to be recognized, but it should work in a Sheet Summary Field:
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
I'm not sure what was wrong the first time but it works now so I must've had something wrong in the syntax. THANK YOU!
-
No problem! 🙂 I'm glad you got it working.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
This was very helpful! Thank you @Genevieve P. !
-
I am having the same issue. The count for Central should be 2 and Northeast 1, but neither values in the box with two selected are being counted. Here's the formula:
=COUNTIF([Land Plan Area]:[Land Plan Area], HAS(@cell, "Central"))
-
Hey @jdpruett
Since "Land Plan Area" is just a text column, try using the CONTAINS function instead:
=COUNTIF([Land Plan Area]:[Land Plan Area], CONTAINS("Central", @cell))
Let us know if that worked!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi SmartSheet Gurus!
I am having a similar however; slightly different issues. I am trying to count multiple text criteria in the same column. I can get the formula to work if I only use one criteria however; haven't been able to get it to work by adding additional criteria (I get 0 value).
Thanks in advance for your assistance :)
Nick Blocker - Analytics Adventurer
-
Hi @NickBlocker
It looks like your column is single-select, and you're looking to count rows with In Progress and row On Hold, is that correct? If so, you can add together two metrics for your total:
=COUNTIFS(Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold")
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
That worked like a charm!!! Below is my end formula :)
=COUNTIFS(Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold") + COUNTIFS(Status:Status, "Clarification") + COUNTIFS(Status:Status, "Pending Assignment") + COUNTIFS(Status:Status, "Possible Risk") + COUNTIFS(Status:Status, "Late")
Nick Blocker - Analytics Adventurer
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!