Trying to Count Project Assignments with Multiple Resources Assigned

Amanda1024

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?

Genevieve P.

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

Genevieve P.

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

Amanda1024

Thanks @Genevieve P. !!! This totally worked. Super grateful to you!! 😀

Genevieve P.

I'm glad I could help! 🙂