Counting number of tasks per Assigned To contact

brenttopa · 09/09/19

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.

计算任务被分配给scre联系enshot.png

Paul Newcome · 09/09/19

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å · 09/10/19

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

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

Paul Newcome · 09/10/19

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å · 09/10/19

Yes, that makes more sense!

brenttopa · 09/10/19

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 · 09/10/19

The CHILDREN function cannot be used in cross sheet references.

Andrée Starå · 09/10/19

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

What do you want the formula to do?

brenttopa · 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å · 09/11/19

Ok.

There are at least three ways to structure it.

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.
You could use the new feature called, Sheet Summary and the Sheet Summary Report to the numbers from each sheet.
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 · 09/11/19

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å · 09/12/19

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 · 04/12/21

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.

Counting number of tasks per Assigned To contact

Comments

Help Article Resources

Categories