Derive status based on status selected for child rows on multi level

svijay22

Hi All,

I have a testing sheet which has Test Scenarios (Level 0 -> Grandparent), test cases (Level 1 -> Parent) and test steps (Level 2 -> children). I am trying to formulate the status for test scenarios and test cases based on the status of all the test steps.

My current formula is stated below. It is working perfectly for the test case (Level 1). However is calculating completely wrong for test scenarios (Level 0). My guess is because of blank values in dark and light blue rows in the test result column. The Test result column is a drop down with possible values - Passed, In Progress, Failed, Blocked, Not started.

Can somebody please help? Also pasted a snippet of my sheet below, if it helps.

Current formula -

=IF(Level@row = 1, IF(COUNTIFS(CHILDREN([Test Result]@row), "Blocked") > 0, "Blocked", IF(COUNTIFS(CHILDREN([Test Result]@row), "Failed") > 0, "Failed", IF(COUNTIFS(CHILDREN([Test Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Passed"), "Passed", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Passed", @cell = "Not Started")), "In Progress")))))), IF(Level@row = 0, IF(COUNTIFS(CHILDREN([Test Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN(), "Passed"), "Passed", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Passed", @cell = "Not Started", @cell = "Blocked", @cell = "Failed")), "In Progress"))))))

Austin Smith

See if this works - works on the little data you provided, may break with more inputs

Change the if(level@row = 0... section to use [scenario result]@row as reference, not [test result]

=IF(Level@row = 1, IF(COUNTIFS(CHILDREN([Test Result]@row), "Blocked") > 0, "Blocked", IF(COUNTIFS(CHILDREN([Test Result]@row), "Failed") > 0, "Failed", IF(COUNTIFS(CHILDREN([Test Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Passed"), "Passed", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Passed", @cell = "Not Started")), "In Progress")))))), IF(Level@row = 0, IF(COUNTIFS(CHILDREN([Scenario Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN(), "Passed"), "Passed", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN([Scenario Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN([Scenario Result]@row), OR(@cell = "Passed", @cell = "Not Started", @cell = "Blocked", @cell = "Failed")), "In Progress"))))))

Austin Smith

See if this works - works on the little data you provided, may break with more inputs

Change the if(level@row = 0... section to use [scenario result]@row as reference, not [test result]

=IF(Level@row = 1, IF(COUNTIFS(CHILDREN([Test Result]@row), "Blocked") > 0, "Blocked", IF(COUNTIFS(CHILDREN([Test Result]@row), "Failed") > 0, "Failed", IF(COUNTIFS(CHILDREN([Test Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Passed"), "Passed", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Passed", @cell = "Not Started")), "In Progress")))))), IF(Level@row = 0, IF(COUNTIFS(CHILDREN([Scenario Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN(), "Passed"), "Passed", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN([Scenario Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN([Scenario Result]@row), OR(@cell = "Passed", @cell = "Not Started", @cell = "Blocked", @cell = "Failed")), "In Progress"))))))

svijay22

@Austin Smith Thank you so much. This worked as a principle. Scenario Result for Level 0 is now working. However still struggling with blank values in Test Result column. As shown in the screenshot below, if any of the test result values are blank (no value selected from the dropdown), then the scenario result for level 1 is automatically defaulting for PASSED which is not right. I would want if there are any blanks, the scenario result should default to In Progress AND if all are blank, then default to NOT STARTED. I tried adjusting the formula, but sadly no luck. Its as if ISBLANK formula is not recognizing the test result level 0 cells as blank.

This is the formula I updated after incorporating your feedback-

=IF(Level@row = 1, IF(COUNTIFS(CHILDREN([Test Result]@row), "Blocked") > 0, "Blocked", IF(COUNTIFS(CHILDREN([Test Result]@row), "Failed") > 0, "Failed", IF(COUNTIFS(CHILDREN([Test Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), "Passed"), "Passed", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Not Started", ISBLANK(@cell))), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), ""), "Not Started", IF(COUNT(CHILDREN([Test Result]@row)) = COUNTIFS(CHILDREN([Test Result]@row), OR(@cell = "Passed", @cell = "Not Started")), "In Progress"))))))), IF(Level@row = 0, IF(COUNTIFS(CHILDREN([Scenario Result]@row), "In Progress") > 0, "In Progress", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN(), "Passed"), "Passed", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN([Scenario Result]@row), "Not Started"), "Not Started", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN(), "Failed"), "Failed", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN(), "Blocked"), "Blocked", IF(COUNT(CHILDREN([Scenario Result]@row)) = COUNTIFS(CHILDREN([Scenario Result]@row), OR(@cell = "Passed", @cell = "Not Started", @cell = "Blocked", @cell = "Failed")), "In Progress"))))))))