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
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!