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"))))
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
when I select in progress or completed it shows up blank in the parent row.
Help Article Resources
Categories
Check out the Formula Handbook template!