Counting number of tasks per Assigned To contact

brenttopa

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.

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)

 

Andrée Starå

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

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

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).

Andrée Starå

Yes, that makes more sense! 

brenttopa

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

The CHILDREN function cannot be used in cross sheet references.

Andrée Starå

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

What do you want the formula to do?

brenttopa

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å

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?

 

brenttopa

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å

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.

Ollie Cater

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.