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

edited 08/18/20
Tags:

• Employee
edited 08/18/20

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.

• 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?

• Employee

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

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.

="" + ROUND(IF(COUNTIF(DESCENDANTS(Completed8:Completed12), 1) / COUNT(DESCENDANTS(Completed8:Completed12)) > 0, COUNTIF(DESCENDANTS(Completed8:Completed12), 1) / COUNT(DESCENDANTS(Completed8:Completed12)), 0) * 100, 0) + "%"

• Employee

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

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!

• Employee

Awesome! Glad I could help 🙂

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) + "%"

• Employee

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) + "%"