How to count how many users assigned to in a sheet

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

Find more posts tagged with

Reports

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Automated email with number of pending records
Hello, I use Smartsheet in such way that people are asked to provide data on many records and then that data needs to be approved by one other person (always the same person). I would like to set up an automation that, each morning, checks to see if there are records waiting to be approved, and if there are, sends an…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
License Question
I already have a Smartsheet license with my company, but I was recently invited to collaborate on a sheet owned by another group within the same organization. When I accepted, I was told I need to upgrade in order to edit or contribute. Can you please explain why this is required? It seems unreasonable to need multiple…