How can I get parent row roll up of child tasks health status

Lindsay AR

Hi - I am using health status which is determined by project status which is in turn determined by a planned vs actual task progress.

I need the the health status to roll up to the parent row since I can't get an accurate weighted planned % complete in the parent row.

I am using the following formula in child tasks health status:

Delayed = yellow

Off Track or Overdue = Red

Completed or In Progress = Green

On Hold or Not Started = Gray

=IF(Status@row = "Delayed", "Yellow", IF(OR(Status@row = "Off Track", Status@row = "Overdue"), "Red", IF(OR(Status@row = "Completed", Status@row = "In Progress"), "Green", IF(OR(Status@row = "On hold", Status@row = "Not Started"), "Gray"))))

I also have a formula for parent rows:

More than 1 red = red

1 red = yellow

more than 1 yellow = yellow

Else = green

=IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green")))

What I need is a formula that puts this all together so that it can be used in project plan template. I also have column to look for hierarchy for a variety of reasons and not sure if that is a good data point to look for. I have tried several variations with my latest being the following but it does not work.

=IF(Hierarchy@row > 0, (COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green", IF(Status@row = "Delayed", "Yellow", IF(OR(Status@row = "Off Track", Status@row = "Overdue"), "Red", IF(OR(Status@row = "Completed", Status@row = "In Progress"), "Green", IF(OR(Status@row = "On hold", Status@row = "Not Started"), "Gray")))))

Paul Newcome

Try something like...

=IF(COUNT(CHILDREN()) > 0, parent row formula, child row formula)

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), child row formula)

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(Status@row = "Delayed", "Yellow", IF(OR(Status@row = "Off Track", Status@row = "Overdue"), "Red", IF(OR(Status@row = "Completed", Status@row = "In Progress"), "Green", IF(OR(Status@row = "On hold", Status@row = "Not Started"), "Gray")))))

Paul Newcome

Try something like...

=IF(COUNT(CHILDREN()) > 0, parent row formula, child row formula)

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), child row formula)

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "Red") > 1, "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", IF(COUNTIFS(CHILDREN(), "Yellow") > 1, "Yellow", "Green"))), IF(Status@row = "Delayed", "Yellow", IF(OR(Status@row = "Off Track", Status@row = "Overdue"), "Red", IF(OR(Status@row = "Completed", Status@row = "In Progress"), "Green", IF(OR(Status@row = "On hold", Status@row = "Not Started"), "Gray")))))

Lindsay AR

Lots of testing and worked like a charm! I must have tried 20 different things and must have just been thinking too hard.

Thank you!!!!

Paul Newcome

Happy to help. 👍️