6

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

Comments

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.

In reply to by Paul Newcome

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. 

In reply to by Westlandadmin

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.

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

In reply to by Mike Wilday

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([email protected], 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.