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.
-
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")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!