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

Find more posts tagged with

Formulas

Project Management

Comments

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.

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…
Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!