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

Options
edited 02/05/24

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

• ✭✭✭✭✭✭
Answer ✓
Options

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

## Answers

• ✭✭✭✭✭✭
Answer ✓
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!