Counting number of tasks per Assigned To contact

My task list contains parent, sub-parent, then children tasks.  The workstream leader is assigned to both the parent and sub-parent tasks, and possibly sub-tasks as well.

 

I need to count the number of tasks per "Assigned To" contact.  It would be ideal if parent tasks are not included in that number, but it's manageable if they are.  

 

I have tried this formula via an external sheet but it is coming up as unparseable: 

 

=SUMIF({External Workspace Name}, [Task Name]1:[Task Name]594,"", [Assigned To]1:[Assigned To]594))

 

The count doesn't have to be captured in a separate sheet.  A dashboard might be a better option.  However, I am not familiar with those yet.

 

Screenshot below to show the different tasks levels.

Count tasks by assigned to contact screenshot.png

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use a COUNTIFS instead of a SUMIFS since you are not actually summing anything, just counting the number of occurrences.

     

    You would also want to create a "helper column" on the source sheet with the formula of

     

    =COUNTI(CHILDREN([Assigned to]@row))

     

    in it.

    .

    Then on the sheet where you are wanting to display the count, you would use something along the lines of

     

    =COUNTIFS({Source Sheet Assigned to}, [Assigned to]@row, {Source Sheet Helper Column}, 0)

     

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Paul missed a letter in the first formula, so here's the correct one.

    =COUNTIF(CHILDREN([Assigned to]@row))

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I actually meant to do just a basic COUNT. Haha. I use COUNTIFS so much that my muscle memory cranks it out before I can stop myself. My mistake this time was not hitting backspace enough. Thanks for the catch (again).

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Yes, that makes more sense! wink

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi Andrée,

     

    I tried implementing your formula in a separate sheet and referencing the column in my workspace containing the "Assigned To" contact names:

     

    =COUNTIF(CHILDREN({Workspace Name}[Assigned To]1))

     

    However, it is coming up as unparseable.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The CHILDREN function cannot be used in cross sheet references.

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    You can't use CHILDREN in a cross-sheet formula.

    What do you want the formula to do?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: andr[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • brenttopa
    edited 09/10/19

    I just need a report that tallies the number of tasks assigned to each of my contacts.  Preferably, I'd like it to reside in a separate sheet so that I can pull reports if needed.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Ok.

    There are at least three ways to structure it.

    1. A section in the sheet(s) counting the assigned to and then cell-linking or using cross-sheet formulas to collect everything in a so-called Master Metric Sheet.
    2. You could use the new feature called, Sheet Summary and the Sheet Summary Report to the numbers from each sheet.
    3. You could use cross-sheet formulas to collect everything in a so-called Master Metric sheet.

    Which method would you prefer? How many sheets do you want to collect the information from?

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • For option #2, can other stats be collected on the Sheet Summary as well?  If so, that would be a great option since I know we will need to collect other information.

     

    Otherwise, option #3 sounds like a good one too.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    In the Sheet Summary, you can collect/add almost (if not everything) that is possible to add to a row in the sheet.

    I'd recommend trying if Sheet Summary would be sufficient and go to option 3 if not.

    Let me know if you have any questions.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • To follow on from this I have a further question. I also need a task count per Owner.

    Would I need to do a summary field with a COUNT for each person? And then I'd want a count of both Not Started and In Progress.

    Really struggling with this, but it feels like there should be a simple solution. My ideal end-game would be a report with the Person's name, how many in progress tasks, how many not started, how many complete.

Help Article Resources