Health for Parent Row Based on Health of Child Rows

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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!