How to count how many users assigned to in a sheet

06/28/19 Edited 12/09/19

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

  • Mike WildayMike Wilday ✭✭✭✭✭

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 07/01/19

    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.

  • 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 NewcomePaul 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 WildayMike 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 NewcomePaul 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([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.

Sign In or Register to comment.