Status Roll Up Formula

I am working on a sheet to track the status of test cases and want to have the status of the subtask roll up to the parent status. The goal is that if 5 tasks are available and 2 "Passed", 1 "In Progress", 1 "Not Started" and 1 "Failed that it shows the status of "In Progress on the parent row. Below is the formula I am working with and can't get it to work. Any Suggestions?

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Not Started"), "Not Started", IF(COUNT(CHILDREN()), = COUNTIFS(CHILDREN(), "Failed"), "Failed", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Passed"), "Complete", "In Progress"))))

Find more posts tagged with

Accepted answers

All comments

Mark Cronk

Try:

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Not Started"), "Not Started", IF(COUNTIFS(CHILDREN(), "Failed")>0, "Failed", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Passed"), IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Complete"), "Complete", "In Progress"))))

Mark

Daniel Melton

when I select in progress or completed it shows up blank in the parent row.

Craig U

I used the above - had this working in older sheet I have no longer access to.

Currently using the formula above - the parent updates when children are "Not Started" only. For all others the parent cell goes blank. I checked, there is no conditional formatting…

Found error - new formula

=IF(COUNT(CHILDREN(Status@row)) > 0, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Not Started") = COUNT(CHILDREN(Status@row)), "Not Started", "In Progress")))

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.