Count Number of Resources in Assigned To

Lisa Matthews2

Hello,

I am having trouble creating a "count" formula for the following scenario (example):

1. "Assigned To" [Contacts List] column is set to "Allow multiple contacts per cell"
2. Four contacts are selected in "Assigned To"
3. Allocation % is 20% (could be any percentage)
4. Duration is 10 days
5. Need a formula to provide a count total of the number of resources in the "Assigned To" column
6. Need to calculate # hours
   1. total hours for task
   2. total hours by resource

Can anyone help? Please? Thank you so much.

--Lisa

Paul Newcome

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.

Lisa Matthews2

@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

Paul Newcome

Happy to help. 👍️

NavyPilot

@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?

Paul Newcome

@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?

NavyPilot

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.

Paul Newcome

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.