Count check boxes in child rows and convert to percentage
I set up a sheet with a checkbox column called "Confirm complete". I started with 5 children under the main task and got this formula to work in counting the check boxes and displaying a percent complete for that group of tasks:
=COUNTIF(CHILDREN(), 1) / 5 * 100 + "%"
The project has now expanded and there are two sections each with 5 subtasks and now there is a parent row above that. I tried using the same formula and just changing the denominator to 10 instead of 5. But it is not recognizing any of the check boxes when they are marked. Trying to solve for the yellow cell in the screen below. should read 20% ( 2 out of 10 sub tasks completed)
Answers

Here's one way to do it. This is an example similar to yours.
Use this formula in the parent cells in the Complete column:
=COUNTIF(CHILDREN(), true) / COUNT(CHILDREN()) * 100 + "%"
Use this formula in the grandparent cell in the Complete column:
=COUNTIF(DESCENDANTS(), true) / (COUNT(DESCENDANTS())  COUNT(CHILDREN())) * 100 + "%"
This technique also provides you a way to have as many child/grandchild rows as you want.
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri  Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Perfect! thanks

Is there a way to use this solution but force the % to round up/down? The decimal decrease button does not seem to work, I assume because the column is a check box... Otherwise this works great

Good morning Joe, I was wondering if you found a solution to this?
Is there a way to use this solution but force the % to round up/down? The decimal decrease button does not seem to work, I assume because the column is a check box... Otherwise this works great
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!