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!
Help Article Resources
Check out the Formula Handbook template!