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
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!