# Count Number of Resources in Assigned To

Options
✭✭✭✭

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
2. total hours by resource

Can anyone help? Please? Thank you so much.

--Lisa

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭
edited 01/05/23
Options

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

Am I missing something?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
edited 01/06/23
Options

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.

• ✭✭✭✭✭✭
Options

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.