Calculate Total % Complete Checked Boxes of all Parent and Child Tasks

Claire Pascavis

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)

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%.
2. 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.
3. As you can see in my formula (above), I want it to:
4. 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!

Tomasz Giba

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.

Paul Newcome

Try

=AVG(CHILDREN())

Claire Pascavis

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!

Paul Newcome

Try entering zeros into the blank rows.

Tomasz Giba

Hi Claire,

Type:

=SUM(CHILDREN()) / COUNT(CHILDREN([Action Items]@row))

into % Complete parent row

Claire Pascavis

@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! 😃

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.

Claire Pascavis

@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!

Claire Pascavis

@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!

Claire Pascavis

@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!!

Tomasz Giba

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: smartsheetpolska@gmail.com

Thanks!

hesham nasr

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

Tomasz Giba

@hesham nasr

Yes, it has been solved.

Paul Newcome

@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.

Tomasz Giba

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.