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

SSI_McKenzie

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.

KPH

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

KPH

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

SSI_McKenzie

https://community.smartsheet.com/discussion/comment/417886#Comment_417886

You are amazing! This worked!! Thank you so much!!!

KPH

😍 You are welcome, and thanks for letting me know.