CHILDREN formula in parent row with a Status column

Michal Kugacz

Hi, 

I'm trying to add in a parent row, as a Status cell, this kind of formula:

If any of the children have 'In Progress' or 'Complete' in a 'Status' cell, then in the parent's 'Status' cell, should be 'In Progress' OR all of them have 'Complete' in a 'Status', then return 'Complete' OTHERWISE return 'Not Started' (doesn't matter Children are blank or have 'Not Started' staus)

I have no idea how to start... and I think this info in a parent status cell should be the fundamental scope of information.

Thank you in advance

Andrée Starå

Hi Michal,

Try something like.

Hi James,

Try this.

=IF(COUNT(CHILDREN(Primary@row)) = COUNTIF(CHILDREN(); "Complete"); "Complete"; IF(OR(COUNTIF(CHILDREN(); "In Progress") > 0; COUNTIF(CHILDREN(); "Complete") > 0); "In Progress"; "Not Started"))    

The same version but with the below changes for your and others convenience.  

=IF(COUNT(CHILDREN(Primary@row)) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", "Not Started"))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting