Count completed task if all sub-task is completed
Hi,
Hope someone can help. I've tried a few combination formulas but still not able to figure out.
I'm trying to count the Completed activities, with the condition all sub-tasks under the same activity is completed.
Example below:
Activity-AA have 2 sub-tasks.
Answers
-
Hey @Kelvin
From your screenshot it is difficult to see what is a sub task and what is not, and if it is a sub task then what is it linked to. Are there child rows present and we cannot see the hierarchy in this view?
Kelly
-
Hi @Kelly Moore
Thanks for responding, hope this is clearer.
Primary - Main Task
- Division/Group - sub-task
Summary: 4 Primary activities (AA, PPP, Safety, SS) & 2 Completed (Safety & SS)
Tried integrating IF/COUNTIF into below formula but doesn't work.
=COUNT(DISTINCT(COLLECT({Primary Activities}, {Status}, ="Completed")))
-
Hey @Kelvin
You'll need a helper column (a checkbox will do) so you can make the evaluation. You'll then be able to count all the checkmarks in the end
Use this formula in the helper column.
=IF(AND(MAX(COLLECT([Row ID]:[Row ID], Task:Task, Task@row)) = [Row ID]@row, COUNTIFS(Task:Task, Task@row) = COUNTIFS(Task:Task, Task@row, Status:Status, "Completed")), 1)
To make sure rows aren't being counted multiple times, the formula first looks to see if it is the max row (you could also use the created date if you had that in your sheet instead of [Row ID]), then it checks to see if the count of the specific activity equals the count of those activities that have status = Completed. If all of that is true, check the column.
You can either then sum these checkmarks in a formula or within your Report you can Summarize this column with Count.
Will this work for you?
Kelly
-
Thanks @Kelly Moore
Not sure if i input it correctly, doesn't seem to work.
=IF(AND(MAX(COLLECT([Entry #]:[Entry #], [Activity Description]:[Activity Description], [Activity Description]@row)) = [Entry #]@row, COUNTIFS([Activity Description]:[Activity Description], [Activity Description]@row) = COUNTIFS([Activity Description]:[Activity Description], [Activity Description]@row, Status:Status, "Completed")), 1)
-
It works!! I changed it to Modified date as per your recommendation. I believe previous issue was the prefix for the entry#. Thanks a lot @Kelly Moore
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!