COUNTIF with HAS Formula in Sheet Summary

I am trying to do a COUNTIF in the Sheet Summary for a column that is a Multi Select Dropdown. I know that you are supposed to use the HAS function, but I can only seem to get that to work in the sheet with @Cell. Any suggestions?


=COUNTIF([Department of Concern]:[Department of Concern], HAS([Department of Concern]:[Department of Concern], "PCC"))


The formula gives me an answer of 0 but there are several cells that have PCC selected.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Julie Tausch

    Are you receiving a specific error when you use @cell? If so, is it possible that the syntax isn't quite right?

    @cell will need to be typed fully in lower-case in order for it to be recognized, but it should work in a Sheet Summary Field:


    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

  • Try =COUNTIF([Department of Concern]:[Department of Concern], "PCC") without the has.

  • I tried that, but it only counts the cells that ONLY have "PCC" selected. If there is more than one choice selected, it doesn't show in the total. I should have 12 but I get 9 using that formula.

  • Hi @Julie Tausch

    You're right, you will need to use @cell in your HAS function to indicate that it should look into each of the cells in the previously stated range.

    Try this:

    =COUNTIF([Department of Concern]:[Department of Concern], HAS(@cell, "PCC"))


    Cheers!

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • The problem is that @cell doesn't work in the Sheet Summary tab.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Julie Tausch

    Are you receiving a specific error when you use @cell? If so, is it possible that the syntax isn't quite right?

    @cell will need to be typed fully in lower-case in order for it to be recognized, but it should work in a Sheet Summary Field:


    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • I'm not sure what was wrong the first time but it works now so I must've had something wrong in the syntax. THANK YOU!

  • No problem! 🙂 I'm glad you got it working.

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Prime George
    Prime George ✭✭✭✭

    This was very helpful! Thank you @Genevieve P. !

  • I am having the same issue. The count for Central should be 2 and Northeast 1, but neither values in the box with two selected are being counted. Here's the formula:

    =COUNTIF([Land Plan Area]:[Land Plan Area], HAS(@cell, "Central"))



  • Hey @jdpruett

    Since "Land Plan Area" is just a text column, try using the CONTAINS function instead:

    =COUNTIF([Land Plan Area]:[Land Plan Area], CONTAINS("Central", @cell))

    Let us know if that worked!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • NickBlocker
    NickBlocker ✭✭✭
    edited 12/18/23

    Hi SmartSheet Gurus!

    I am having a similar however; slightly different issues. I am trying to count multiple text criteria in the same column. I can get the formula to work if I only use one criteria however; haven't been able to get it to work by adding additional criteria (I get 0 value).

    Thanks in advance for your assistance :)



    Nick Blocker - Analytics Adventurer

  • Hi @NickBlocker

    It looks like your column is single-select, and you're looking to count rows with In Progress and row On Hold, is that correct? If so, you can add together two metrics for your total:

    =COUNTIFS(Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold")

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • NickBlocker
    NickBlocker ✭✭✭

    Hi @Genevieve P.

    That worked like a charm!!! Below is my end formula :)

    =COUNTIFS(Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold") + COUNTIFS(Status:Status, "Clarification") + COUNTIFS(Status:Status, "Pending Assignment") + COUNTIFS(Status:Status, "Possible Risk") + COUNTIFS(Status:Status, "Late")

    Nick Blocker - Analytics Adventurer

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!