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!
Answers
-
Try something like this:
=COUNTIFS({Master Schedule Range 2}, [Assigned to]@row, {Master Schedule Range 3}, OR(@cell = "In Progress", @cell = "Not Started"))
-
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?
-
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"))
-
@Nic Larsen you are incredible, worked like a charm! Thank you so so much 😁🤗
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!