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!

Find more posts tagged with

Formulas

Project Management

Accepted answers

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.

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.