How to count how many users assigned to in a sheet

Westlandadmin

Hello,

I have multiple projects and each project is in its own sheet. I am trying to create a summary sheet (to later show in the dashboard) that shows how many people are assigned to each project currently. Is there a formula for this or a feature? Thanks

Mike Wilday

Check out this recent post I just answered related to this topic. Its totally doable. 

• https://community.smartsheet.com/discussion/need-count-project-managers-and-number-projects

Paul Newcome

How are you assigning people to the projects? Is it in a Contact type column? Is there the possibility of having multiple contacts in one cell?

 

Because you are trying to count people per project, and each project has it's own sheet, you may need to make a few minor tweaks to Mike's solution.

Westlandadmin

I'm using a Contacts column and I would also like to count if there are multiple people assigned per task/line. It is more useful for me to be able to see how many people are assigned to per sheet (including the lines with multiple people assigned) than to count how many people per task. 

Paul Newcome

To accomplish this, you would need to parse out the rows with multiple contacts then establish your "first occurrence" names and mark them. Once you have done that you can then count your marks to get your total number of people assigned to each sheet.

Mike Wilday

@Paul, wouldn't some combination of the FIND function, in combination with the COUNTIF function, work for counting using a similar method as in my original post? 

Paul Newcome

I thought about this, but the poster isn't looking for how many times "John Doe" appears in the assigned to column. They are looking for how many different unique values there are in the column.

 

Thinking more on it though...

 

If you had a comprehensive list of everyone that COULD be assigned to the sheet, you could skip the COUNTIFS and just use an IF/FIND on a checkbox then tally up how many boxes are checked. Something like

 

Name                               Checkbox

John Doe

Jane Doe

John Smith

Jane Smith

.

Then in the checkbox column use something along the lines of 

 

=IF(FIND(Name@row, JOIN({First Sheet's Assigned To Column}), ",") > 0, 1)

.

This will check the box if they are assigned to the sheet. The only catch is having a comprehensive list of everyone that could possibly be assigned.