Calculate total % for Children rows where % Complete column is being populated by a formula

I have a Status Column and % Complete Column. I am using the following formula in the % Complete column to auto populate the % Complete.

=IF(Status@row = "Complete", "100%", IF(Status@row = "In Progress", "25%", IF(Status@row = "Not Started", "0%", IF(Status@row = "Out of Scope", "100%"))))

But when I try to calculate the total % complete for the children rows into the parent row I keep getting 0. I have tried several different paths and haven't found the solution.

If I remove the formula from the % Complete field and enter the percentage manually, I can then use this formula to calculate the overall % for the children rows.


But I want to auto populate the % Complete field based on the status that is selected.


  • @PottsD

    I am no expert by any means but maybe try =SUM(DESCENDANTS()). I often try the different family formulas if I don't get the results I desired.

    Also if you have start date, end date, and duration columns those need to be filled in for the parent field to calculate % on my sheets.

    Hope one of those two fix your issues.

  • PottsD
    PottsD ✭✭

    Thank you J.Barrow, using Descendants did not work unfortunately.

    I am not allowing Smartsheets to calculate the % complete since it does require dates and duration to be entered and that is not always being entered. I am creating my own % complete for the project by assigning a weight to each task and then calculate the overall % complete for the project, now I am just trying to calculate the different phases % complete.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!