Calculate Total % Complete Checked Boxes of all Parent and Child Tasks
Project: I have a massive checklist we use for event planning (700+ rows!) and want to be able to know how far along in the planning we are percentage wise for one particular event.
Goal: I want the Parent row %s to be the total percentage complete of all their Children tasks. The parent row is setup as an event planning task (think Registration, Marketing, etc.). Then I want to be able to use this same formula in the Grandparent Row to calculate the % of the total event planning completed so far.
Current Workflow (Example using Parent Row 1)
 Once a task (shown as "Child 1" under Parent Row 1) is completed, I check a box [✓ Completed] then it makes the next column [% Complete] equal 100%.
 All the tasks below are summed up in the parent row Formula: =SUM([% Complete]3:[% Complete]7) / 5 to show the percentage equals 20% since only 1 of 5 tasks is complete.
 As you can see in my formula (above), I want it to:
 Formula = Sum (Count % total of all the child rows below  so if I add or delete a row, it does it automatically) / # of Child Rows = Calculate the % complete.
I've been manually entering this, but I know there has to be a more efficient way with a formula. I've spent hours trying to figure out the right formula, but I can't. Thanks in advance to anyone who can help me solve this!
Best Answers

Hi Claire,
Type:
=SUM(CHILDREN()) / COUNT(CHILDREN([Action Items]@row))
into % Complete parent row
Tomasz Giba

@Claire Pascavis if you want to count children and grand children change function CHILDREN() to DESCENDANTS()
=SUM(DESCENDANTS()) / COUNT(DESCENDANTS([Action Items]@row))
However it may not work exactly as you need it for a number of reasons. If you need to adjust it to your specific needs just let me know, I probably can help.
Tomasz Giba
Answers


I appreciate the idea, Paul! I was hoping it would solve my issue, but unfortunately, adding =AVG(CHILDREN()) to the Parent Row 1 Formula in the % Complete column did not work  it just says 100% instead of calculating based on the # of children. I'm open to any other suggestions!


Hi Claire,
Type:
=SUM(CHILDREN()) / COUNT(CHILDREN([Action Items]@row))
into % Complete parent row
Tomasz Giba

@Paul Newcome Thank you for taking the time to try and help me! I really appreciate it!
@Tomasz Giba THANK YOU, THANK YOU, THANK YOU!!! You solved my formula problem! I have tried to solve this individually for almost 3 years now so I am very thankful I came across the community and posted it! Seriously, this helps sooooo much! I appreciate your time and help!!
What I Learned: The child columns must all be in a line with no indents. If you have any indents, the formula Tomasaz Giba provided =SUM(CHILDREN()) / COUNT(CHILDREN([Action Items]@row)) will NOT work and will only calculate the direct children, not the "grandchildren." If you need separation between Child section, I recommend coloring your rows or using underline.
Hope this helps someone else!! If it does, comment below so we know it was useful to others! 😃

@Claire Pascavis if you want to count children and grand children change function CHILDREN() to DESCENDANTS()
=SUM(DESCENDANTS()) / COUNT(DESCENDANTS([Action Items]@row))
However it may not work exactly as you need it for a number of reasons. If you need to adjust it to your specific needs just let me know, I probably can help.
Tomasz Giba

@Tomasz Giba That seems to work! I'll make updates to my Smartsheet and let you know if it works all the way through! Thank you!

@Tomasz Giba If you're still willing to help me solve this, I would appreciate it! It's SO CLOSE, but not quite there.
When I put =SUM(DESCENDANTS()) / COUNT(DESCENDANTS([Action Items]@row)) into the blue parent header rows, it worked perfectly calculating all the children and grandchildren/descendants no matter if they were indented or not.
Unfortunately, when I added =SUM(DESCENDANTS()) / COUNT(DESCENDANTS([Action Items]@row)) to the gray row to calculate total % progress of everything (blue rows and all their descendants), it showed 0% no matter how many rows were checked below.
Thanks for all your help so far!

@Tomasz Giba Do you have any additional suggestions for me regarding my July 29th question. I'm still trying to figure it out! Thank you!!

Hi @Claire Pascavis,
Yes, I'm willing to finalize this formula.
The quickest way to do it is by sharing a copy of your sheet (of course with all data deleted).
Please do this:
• Save your sheet as a new copy
• Delete all columns not involved in this formula
• Delete most of the rows, just leave a few rows that will represent the structure that you use (which is as you have in your first post: grandparent, parent, child)
• make all data generic
• share this sheet to my email: [email protected]
Thanks!
Tomasz Giba

@Tomasz Giba did you solve it? i have the same problem


@Tomasz Giba Are you able to share the solution? It seems as if @hesham nasr is looking for some help with this same problem as well.

The solution for this specific sheet / problem is:
=SUM(CHILDREN()) / COUNT(CHILDREN([Action Items]@row))
Claire mentioned that this didn't worked for her but when she shared a sheet with me and I entered it, it worked. I'm not sure what was wrong with her sheet.
Tomasz Giba
Help Article Resources
Categories
Check out the Formula Handbook template!