% complete based on children task status

Hi Community!

I'm hoping Paul or Tomasz or any other wonderful Smartsheet guru can help us.

We have a project plan and we'd like to indicate the % complete for each phase based on the status count of the children tasks (as opposed to by date or duration).

For example, we have Phase 1 and Phase 2 with four subtasks each in various statuses. Phase 1 has 3 Complete and 1 Blocked. We'd like a % complete column to show 75% based on 3/4 for Phase 1. Phase 2 has 2 Complete and 2 In Progress. We'd like the % complete to reflect 50%.

I added a couple of "helper" columns while I tried numerous different formulas before finally giving in to ask the community. Task Count: =COUNT(CHILDREN()). Phase level: =COUNT(ANCESTORS()).

Thanks in advance!

Angie

Find more posts tagged with

Accepted answers

KPH

I do this without helper columns and wonder if this would work for you

=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNT(DESCENDANTS(Status@row))

It is basically counting how many of the descendants of the row are "Completed" and then dividing that by the total number of descendants.

Depending on your data, you might want to specify which statuses are included in the denominator and could do so with this:

=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNTIF(DESCENDANTS(Status@row), OR(@cell = "Complete", @cell = "In Progress", @cell = "Not Started"))

All comments

KPH

I do this without helper columns and wonder if this would work for you

=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNT(DESCENDANTS(Status@row))

It is basically counting how many of the descendants of the row are "Completed" and then dividing that by the total number of descendants.

Depending on your data, you might want to specify which statuses are included in the denominator and could do so with this:

=(COUNTIF(DESCENDANTS(Status@row), "Complete")) / COUNTIF(DESCENDANTS(Status@row), OR(@cell = "Complete", @cell = "In Progress", @cell = "Not Started"))

AngieChristophersen

Thanks so much @KPH! That worked for our needs and was much simpler than I was making it out to be! I appreciate you! :)

KPH

And I appreciate being appreciated. Thank you!

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.