Parent/Child Formula Help

christy.thompson106676

Hi - hoping to get help crafting a formula. I'm using Smartsheet for systems testing and need an easy way to identify when sets of scenarios are complete or still in progress. The rows in the sheet are in hierarchies with the parent row representing the overall scenario description and the child rows representing the individual steps that need to be executed. Each row has a status column. In another column, I need a formula that looks at the status column and returns the value of Complete or In Progress for the entire scenario taking into consideration the parent + child statuses. Here's the list of statuses whether it would be considered In Progress or Complete:

Unexecuted = In progress

In Progress = In Progress

Retest = In Progress

Fail = In Progress

Pass = Complete

Fail - passed on other scenario = Complete

Out of Scope = Complete

Deferred = Complete

So basically, what I'd like in this other column (I think) is something along the lines of: if the parent or child includes any of the In Progress statuses, return In Progress, otherwise return Complete.

Thank you so much in advance - I'm very intermediate creating formulas like and might be able to figure this out but it would literally take me an hour or two of trial and error!

Mark Cronk

Hi @christy.thompson106676 ,

Fir the parent rows try:

=IF(Count(children()) = countif(Cildren(),="Complete"),"Complete, "In Progress")

The formula counts the children and then the children that are complete. If both are equal the parent is complete. For this to work all of the children have to have data in the row.

Partial answer?

Mark