Status Updates for Parent rows of Children

Carrie Tortorella

I would like the parent of fields in a Status row to reflect Complete when all children are marked Complete or Cancelled. If all children are marked "Not started", then the parent should be marked "Not started". Otherwise, if there is a combination in status for children other than above, the parent should be marked "In progress". I used the formula below but it seems not to follow what I want it to do. Can anyone guide me to my error(s)?

=IF(COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN()), "Not started", IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "In progress") > 0, "In progress", "Complete"))))))

Paul Newcome

Try something like this:

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Cancelled")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Paul Newcome

Try something like this:

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete", @cell = "Cancelled")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

Carrie Tortorella

Thank you - that worked!

Paul Newcome

Happy to help. 👍️