Roll up Fail

topazfae

I have a test scenario as a parent and a sub-task as a child. So let's say, I have 11 sub tasks under the parent, if one of those 11 children has fail in its status, the parent will say fail. If all say pass, then the parent will say pass.

I couldn't get the formula to work what I wanted it to do.

Laurie Olson

Here, try this formula on the parent task.

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail"

Laurie Olson

Here, try this formula on the parent task.

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail"

topazfae

Thanks it works - I added one more - =IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", "Pass")

Laurie Olson

I don't see a test in there to ensure all tests were run. If one task has neither passed nor failed, your logic above will reflect all passed.

Laurie Olson

You need something like this to ensure all have passed.

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Pass"), "Pass", "Incomplete"))

For all tests being run, you need values Pass, Fail, Incomplete(or value like this).

IF statements run in order from left to right until they succeed. So if the value is "Fail", the rest of the logic does not run. If there are no failures, it goes to the next IF statement.