Count formula

C.Beaudoin

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!

Jeff Reisman

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.

Jeff Reisman

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.

C.Beaudoin

Wow, thank you so much!!! That was extremely helpful - I was looking at the wrong function. Thank you!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327648#Comment_327648

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!)