Status Roll Up Formula

Daniel Melton

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

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

Hi

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