Trying to Count Project Assignments with Multiple Resources Assigned
Hello,
I am trying to create a formula that counts all projects assigned to each of my team members. When a project is assigned to only one of them, this is no issue. However, there are some projects where more than one team member is assigned, and I want the formula to recognize this to accurately reflect in my dashboard widget.
In the image below, you can see the sheet reflects three resources assigned to one project.
Here's how my formula is currently set up for each resource: =COUNTIFS({Assigned To}, "Trisha Miller", {Approval Status}, OR(@cell = "In Progress", @cell = "Not Started", @cell = "Blocked", @cell = "Paused")).
So this formula is counting all projects where Trisha is assigned, but it's not including those like below where she's assigned along with other resources. In my dashboard, it appears as though she has less projects assigned to her because of this.
Any suggestions?
Best Answer
-
Hi @Amanda1024
You can use the HAS function for this! HAS will check each cell to see if it has that person, listed with other selections or on its own.
Try:
=COUNTIFS({Assigned To}, HAS(@cell, "Trisha Miller"), {Approval Status}, OR(@cell = "In Progress", @cell = "Not Started", @cell = "Blocked", @cell = "Paused"))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Amanda1024
You can use the HAS function for this! HAS will check each cell to see if it has that person, listed with other selections or on its own.
Try:
=COUNTIFS({Assigned To}, HAS(@cell, "Trisha Miller"), {Approval Status}, OR(@cell = "In Progress", @cell = "Not Started", @cell = "Blocked", @cell = "Paused"))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Genevieve P. !!! This totally worked. Super grateful to you!! 😀
-
I'm glad I could help! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!