Count formula
I am trying to create a formula where it will count how many rows are assigned to HR within a multiselected 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([MultiSelect Column Name]:[MultiSelect Column Name], HAS(@cell, "HR"))
HAS function looks for whole values inside a multiselect 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([MultiSelect Column Name]:[MultiSelect Column Name], HAS(@cell, "HR"))
HAS function looks for whole values inside a multiselect 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
Check out the Formula Handbook template!