Count of all rows that do NOT have a completed status


I'm trying to get a count of all cases that are not complete (aka open), to use on a dashboard metric widget. Eventually I'd probably like to be able to have a metric widget showing the count of cases that are in each status EXCEPT complete. I've dug around on the communities and I can't seem to get it to work.

This is what I have so far but in gives me an invalid ref error:

It should in this case kick back 5. I had another version of the equation and it gave me 2.



  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    Hi @Sarah Daley,

    It may simpler than what you are attempting.

    See if this works for you. =COUNTIF(Status:Status, <>"Complete")

    You're example shows Status as a cross-sheet reference. If that's the case, substitute that in the above formula.

    Hope this helps,


  • S-Jacob
    S-Jacob ✭✭✭

    You need to define your range as Status:Status. The two ways you can get what you are looking for are either:

    =COUNT(Status:Status) - COUNTIF(Status:Status, "Complete")


    =COUNTIF(Status:Status, "<>Complete")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!