Health for Parent Row Based on Health of Child Rows

Josh Martin

Hello, I'm in need of some assistance to address the following problem.

Objective: I have a series of Tasks which nest into Milestones, and those Milestones then nest into Project Components.

I've successfully automated Health for Tasks following a set of desired conditions:

I'd now like to automate Health for Milestones and Project Components based on the following:

• Tasks are treated as Children of Milestones.
• Milestones are Children of Project Components.
• If any Children have Health that is "Red," then Parent should default to "Red."
• If all Children have Health that is "Blue," then Parent should also be "Blue."
• If majority of Children have Health that is "Green" and there are no Children that are "Red," then Parent should also be "Green."
• If majority of Children have Health that is "Yellow" and there are no Children that are "Red," then Parent should also be "Yellow."

It's important to note that my Milestone and Project Components are set using Checkbox Columns to assist my team with automated formatting for these rows.

Problem: I believe the following formula would define the desired conditions for Milestones & Project Components:

=IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))

However, when I try to combine this formula with my formula for Tasks so they can operate as a single Column Formula, I end up with only Health for the Milestones and Projects. The Health for Tasks seem to disappear altogether.

Any assistance to understand how I can combine these two formulas as one would be greatly appreciated. Thank you.

Paul Newcome

You would need to nest them both within another IF statement to say that if there are children then run the parent row formula, otherwise run the child row formula.

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