Count number of resources per project

HL
HL
edited 12/09/19 in Smartsheet Basics

Hi,

I'm trying to count number of resources assigned to a project but I'm not sure how to go about it, when there is a duplicate. Meaning, if John Doe is assigned to 5 tasks, Jane Doe is assigned to 3 tasks, and Billy Bob is assigned to 1 task, is there a way to count the number of resources assigned to the project, which is only 3 in this case? Thanks in advance for the help.

Comments

  • Hi,

    There isn't a way to do this if you're assigning multiple resources in a single Contact column. When you have a moment, please submit a Product Enhancement Request using the form under Quick links to the right of the community site to let our Product team know that you'd like to have a way to count this.

  • HL
    HL

    Hi Shaine,

    Thanks for responding. No, i did not assign multiple resources in each contact cell. What I'm trying to do is find out how many contact / resource has been assigned to the entire project (single smartsheet plan). For example:

    Task A - John Doe

    Task B - John Doe

    Task C - Jane Doe

    Task D - Billy Bob

    Task E - Jane Doe 

    where Task A - E is in Text / Number column and John, Jane, and Billy are in Contact column (single resource).

    If i'm counting the resource manually, i should get 3. 

    Purpose of this calculation is to report out to stakeholders actual number of resources (not work effort) needed for the project.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 03/27/19

    You are looking to count unique values in the resources column. I would suggest this:

     

    Add a helper column (for this example I will call it Helper and format it as a checkbox type).

     

    In the helper column in row 1 you would enter...

     

    =IF(COUNTIFS([Resource Column]$1:[Resource Column]@row, [Resource Column]@row) = 1, 1)

     

    NOTE: The $1 locks in row 1 for the formula. You would change that number to whatever row number your resources actually start on.

     

    If you dragfill this down the rest of the helper column, it will put a check for each unique entry in the resource column. You can then use a basic

    =SUMIFS(Helper:Helper, 1) 

    to add up all of the checked boxes.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • HL
    HL

    Thank you Paul! I didn't even think about counting it the way you've recommended, it works. I used the formula you've provided with slight modification to ignore blank cell.

    =IF(AND(NOT(ISBLANK(Resource@row)), COUNTIFS(Resource$1:Resource@row, Resource@row) = 1), 1)

    Thanks again for your help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com