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.
Comments
-
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)
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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).
-
Yes, that makes more sense!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
The CHILDREN function cannot be used in cross sheet references.
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!