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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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.


    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!