Need help with the correct formula excluding "On Hold" items


I have a SS Sheet with the "Task Name", "Assigned To" and "Status". Please see all the Status below,

(New, In Progress, Awaiting Approval, In Revision, Approved, Complete, On Hold, & Not Started)

I am currently using this formula to count the number of Tasks for each person on my team:

=COUNTIF([Assigned To]:[Assigned To], HAS(@cell, "McKenzie.."))

For example, let's say I have 10 Tasks, HOWEVER, I want to exclude all the "On Hold" items, which the accurate number would be 8 Tasks. Also, please keep in mind that in some Tasks, there are multiple Assignees. I am using a multi-select dropdown list to add multiple ppl to a certain task

I have used all different types of formulas like, =COLLECT, COUNTIF, COUNTIFS, and nothing seems to work.

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @SSI_McKenzie

    COUNTIFS should give you what you need. It sounds like your Status column is not multi select so all you need to do to exclude On Hold is add the Status range and the criteria <>"On Hold" (doesn't equal "On Hold")

    =COUNTIFS([Assigned To]:[Assigned To], HAS(@cell, "McKenzie.."), Status:Status, <>"On Hold")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!