Looking to take a parent row on the pulled column and put a percent complete of the children rows.
Best Answer

Hi Chris,
To calculate the % complete in the checkbox column (e.g. to use a formula to populate a numeric value in a column expecting boolean values), try using this type of formula in the parent row cell:
="" + ROUND(IF(COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)) > 0, COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)), 0) * 100, 0) + "%"
Using the set of quotation marks at the beginning of the formula with the plus (+) sign before the rest of the formula will allow you to populate the formula result in the checkbox column you're referencing.
Thanks,
Ben
Answers

Hi @Chris Nicklaus,
If I'm understanding this correctly, you're looking for a way to calculate a percentage of checked boxes on child rows in your sheet. If this is the case, as long as your "% Complete" column isn't being used for Dependencies you should be able to enter a formula on the parent row to calculate the percentage of checked child row boxes. Something like this should work:
=ROUND(IF(COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)) > 0, COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)), 0) * 100, 0) + "%"
More information on the functions used in this example can be found in the following Help articles:
I hope this helps! If the above doesn't address what you're looking for, please provide some additional details on what you're hoping to accomplish and I'll be happy to assist further.
Thanks,
Ben

I'm looking to take the checkbox row which I'm calling pulled. I would like to put a percentage complete of the children rows into the parent row. So the percent complete is in the same column as the checkbox's.

Hi Chris,
To calculate the % complete in the checkbox column (e.g. to use a formula to populate a numeric value in a column expecting boolean values), try using this type of formula in the parent row cell:
="" + ROUND(IF(COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)) > 0, COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)), 0) * 100, 0) + "%"
Using the set of quotation marks at the beginning of the formula with the plus (+) sign before the rest of the formula will allow you to populate the formula result in the checkbox column you're referencing.
Thanks,
Ben

Now I want to take the Child rows and put a percent complete in the row I'm calling Grandparent I want it to be based off the child rows because the amount of Child rows in each parent group differ. So I want the percent complete to be waited off the over all check boxes
Thanks,

I'm finding the above formula to be unparseable. What am I doing wrong?

Hi @tlong2b
What are your column names? Ben's formula above references a column called "Checkbox". If you have a different column name you'll need to update this portion of the formula to match your sheet.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi Genevieve!
I am trying to do something similar, and have adapted this formula, but it is not calculating correctly, could you help me with what I am doing wrong? I am using the below formula, and screenshot is attached of what I see.
Thanks for any help you can provide!
="" + ROUND(IF(COUNTIF(DESCENDANTS(Completed8:Completed12), 1) / COUNT(DESCENDANTS(Completed8:Completed12)) > 0, COUNTIF(DESCENDANTS(Completed8:Completed12), 1) / COUNT(DESCENDANTS(Completed8:Completed12)), 0) * 100, 0) + "%"

Hey @K Mitchum
Within the DESCENDANTS function you'll want to reference the parent cell, so most likely the cell in the current row. Since you're putting the formula in the same column as you want to evaluate, you actually don't need a reference at all.
="" + ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"
Let me know if that fixes it for you 🙂
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi Genevieve!
Amazing, I think that worked! I will add to all my other sheets now and confirm if I have any more trouble!
Really appreciate your help and quick response!

Awesome! Glad I could help 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Hi Genevieve!
Me again! I have a similar issue with the percent complete calculations on a different sheet, but this time I am wondering if there is something to add that will Exclude the parent rows in the overall total calculations (row "Project 1  PV"). Screenshot below to show what I mean.. I am using the following formula for calculation currently, but it is not showing 100% complete because there are no checkboxes in the 3 parent rows but not sure how to exclude those specific rows here. Thank you!!
="" + ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

Hi @K Mitchum
No problem! We can subtract the count of direct Child rows from your count of Descendants in general. Try this:
="" + ROUND(IF(COUNTIF(DESCENDANTS(), 1) / (COUNT(DESCENDANTS())  COUNT(CHILDREN())) > 0, COUNTIF(DESCENDANTS(), 1) / (COUNT(DESCENDANTS())  COUNT(CHILDREN())), 0) * 100, 0) + "%"
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 361 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!