Count formula

C.Beaudoin · 04/27/22

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 · 04/27/22

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 · 04/27/22

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 · 04/27/22

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

Jeff Reisman · 04/27/22

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

Count formula

Best Answer

Answers

Help Article Resources

Categories