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.

=SUM(CHILDREN())

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

Find more posts tagged with

Accepted answers

All comments

J.Barrow

@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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.