looking for BOTH "In Progress" & "Not Started" Tasks with specific assignee associated

New to Smartsheet so apologies if this doesn't make sense. Looking for a formula that groups all In Progress Tasks and Not Started tasks per Assignee.

Presently I have utilized the following formula to denote Total tasks regardless of status for each Assignee due to allowing multiple contacts per cell:

=COUNTIF({Master Schedule Range 2}, HAS(@cell, "Monish Sakar"))

After finalizing that formula I realized I needed to filter out completed tasks and only show those that are outstanding. I attempted to use the following formula and am returning an unparseable error (I'm sure it's user error on my end):

=COUNTIF({Master Schedule Range 2}, HAS(@cell, "Timm Wikelski"), AND({Master Schedule Range 3} = "In Progress", {Master Schedule Range 3} = "Not Started')

Any assistance would be greatly appreciated!

Find more posts tagged with

Accepted answers

All comments

Nic Larsen

Try something like this:

=COUNTIFS({Master Schedule Range 2}, [Assigned to]@row, {Master Schedule Range 3}, OR(@cell = "In Progress", @cell = "Not Started"))

parkeraviolet

Hi @Nic Larsen thank you for your help!

For some reason it's not catching the cells where there are multiple contacts when I utilize that formula. For example, Timm Wikelski is now showing as '3' when he's included in about 20 other cells. Is there a way to account for each assignee even with multiple contacts in a cell?

Nic Larsen

This should work in a multi-contact column scenario:

=COUNTIFS({Master Schedule Range 2}, FIND([Assigned to]@row, @cell) >= 1, {Master Schedule Range 3}, OR(@cell = "In Progress", @cell = "Not Started"))

parkeraviolet

@Nic Larsen you are incredible, worked like a charm! Thank you so so much 😁🤗

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.