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

Best Answer

Answers

  • Ben Goldblatt
    Ben Goldblatt Employee
    edited 08/18/20

    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.

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

  • tlong2b
    tlong2b ✭✭

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

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

  • Genevieve P.
    Genevieve P. Employee Admin

    Awesome! Glad I could help 🙂

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



  • Genevieve P.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!