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

edited 07/18/22

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!

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

Try

=AVG(CHILDREN())

• edited 07/18/22

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!

• ✭✭✭✭✭✭

Try entering zeros into the blank rows.

• ✭✭✭✭✭

Hi Claire,

Type:

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

into % Complete parent row

Tomasz Giba

• edited 07/19/22

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

• ✭✭✭✭✭

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

• 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

Thanks!

Tomasz Giba

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

• ✭✭✭✭✭

Yes, it has been solved.

Tomasz Giba

• ✭✭✭✭✭✭

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!