Count Number of Resources in Assigned To
Hello,
I am having trouble creating a "count" formula for the following scenario (example):
- "Assigned To" [Contacts List] column is set to "Allow multiple contacts per cell"
- Four contacts are selected in "Assigned To"
- Allocation % is 20% (could be any percentage)
- Duration is 10 days
- Need a formula to provide a count total of the number of resources in the "Assigned To" column
- Need to calculate # hours
- total hours for task
- total hours by resource
Can anyone help? Please? Thank you so much.
--Lisa
Answers
-
To get the count of how many people are assigned, you would use a COUNTM function (link included).
The total hours for the task would be the working hours per day times the duration.
Total hours by resource would be the above total hours for task divided by the number of people assigned.
-
@Paul Newcome Thank you so much! The COUNTM function worked perfectly! I was able to figure out the total hours by resource easily after that!
Many, many thanks!
--Lisa
-
Happy to help. 👍️
-
@Paul Newcome So I have the same issue. I have multiple resources per task and want to count unique resources assigned. But when I use COUNTM, I do not get the correct numbers.
For example, here is a snippet of the column.
=COUNTM(DISTINCT([Lead(s)]1:[Lead(s)]8)) yields 1. Should be 1.
=COUNTM(DISTINCT([Lead(s)]1:[Lead(s)]9)) yields 4. Should be 3.
=COUNTM(DISTINCT([Lead(s)]1:[Lead(s)]11)) yields 6. Should be 3.
Am I missing something?
-
@NavyPilot In your case you will need to parse out the names so that each is in its own column and then count the distinct entries across these helper columns. How many total names would be the max for a single cell?
-
Thanks @Paul Newcome. I'm surprised this isn't easier in Smartsheet as I see quite a few asking for it. Right now the max is about 3 though the list of possible resources is 11. I doubt there'd ever be a situation where all 11 were assigned to a task.
-
You are going to want to insert some helper columns that can later be hidden. The easiest way to do it would be to insert 11 and then use an IF statement in each to output the name if it is found.
=IF(HAS([Lead(s)]@row, "Joe"), "Joe)
Then you would COUNT/DISTINCT on these helper columns.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!