Count formula
I am trying to create a formula where it will count how many rows are assigned to HR within a multi-selected row. HR may appear by itself or with another option. The multiple selected rows are throwing the count off and I cannot seem to figure out how to fix it. I tried using the CountM function, but it is counting everything within the row, not just HR.
Thank you!
Best Answer
-
You'll want to use COUNTIFS with HAS and @cell:
=COUNTIFS([Multi-Select Column Name]:[Multi-Select Column Name], HAS(@cell, "HR"))
HAS function looks for whole values inside a multi-select column. Since we're HAS inside a COUNTIFS, we want the COUNTIFS function to consider the values resulting from HAS working on the column. Using the @cell tells the formula to perform the HAS on every cell in the column, and THEN to use COUNTIFS on the results.
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
-
You'll want to use COUNTIFS with HAS and @cell:
=COUNTIFS([Multi-Select Column Name]:[Multi-Select Column Name], HAS(@cell, "HR"))
HAS function looks for whole values inside a multi-select column. Since we're HAS inside a COUNTIFS, we want the COUNTIFS function to consider the values resulting from HAS working on the column. Using the @cell tells the formula to perform the HAS on every cell in the column, and THEN to use COUNTIFS on the results.
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!
-
Wow, thank you so much!!! That was extremely helpful - I was looking at the wrong function. Thank you!
-
Happy to help. The HAS function help page needs work for sure (I sent in a ticket with improvements for that page like 2 months ago!)
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!