Count number of resources per project

HL

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.

Shaine Greenwood

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

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

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.

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

Happy to help!