Trying to Count Project Assignments with Multiple Resources Assigned



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

  • Genevieve P.
    Genevieve P. Employee Admin
    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.


    =COUNTIFS({Assigned To}, HAS(@cell, "Trisha Miller"), {Approval Status}, OR(@cell = "In Progress", @cell = "Not Started", @cell = "Blocked", @cell = "Paused"))




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!