Count number of resources per project
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.
-
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.
-
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.
-
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!
-
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives