% complete based on children task status
Hi Community!
I'm hoping Paul or Tomasz or any other wonderful Smartsheet guru can help us.
We have a project plan and we'd like to indicate the % complete for each phase based on the status count of the children tasks (as opposed to by date or duration).
For example, we have Phase 1 and Phase 2 with four subtasks each in various statuses. Phase 1 has 3 Complete and 1 Blocked. We'd like a % complete column to show 75% based on 3/4 for Phase 1. Phase 2 has 2 Complete and 2 In Progress. We'd like the % complete to reflect 50%.
I added a couple of "helper" columns while I tried numerous different formulas before finally giving in to ask the community. Task Count: =COUNT(CHILDREN()). Phase level: =COUNT(ANCESTORS()).
Thanks in advance!
Angie
Best Answer
-
I do this without helper columns and wonder if this would work for you
=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNT(DESCENDANTS(Status@row))
It is basically counting how many of the descendants of the row are "Completed" and then dividing that by the total number of descendants.
Depending on your data, you might want to specify which statuses are included in the denominator and could do so with this:
=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNTIF(DESCENDANTS(Status@row), OR(@cell = "Complete", @cell = "In Progress", @cell = "Not Started"))
Answers
-
I do this without helper columns and wonder if this would work for you
=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNT(DESCENDANTS(Status@row))
It is basically counting how many of the descendants of the row are "Completed" and then dividing that by the total number of descendants.
Depending on your data, you might want to specify which statuses are included in the denominator and could do so with this:
=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNTIF(DESCENDANTS(Status@row), OR(@cell = "Complete", @cell = "In Progress", @cell = "Not Started"))
-
Thanks so much @KPH! That worked for our needs and was much simpler than I was making it out to be! I appreciate you! :)
-
And I appreciate being appreciated. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!