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: [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: an[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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!