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

Claire Pascavis
edited 07/18/22 in Formulas and Functions

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!

Best Answers

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer ✓

    Hi Claire,


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

    into % Complete parent row

    Tomasz Giba

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    edited 07/21/22 Answer ✓

    @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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!