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.

Find more posts tagged with

Accepted answers

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

All comments

KPH

Hi @SSI_McKenzie

=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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.