How to have child rows calculate health and then roll that up to parent rows with different criteria



I have a "Profile Health" column that is measuring the health of all rows using this formula:

=IF([Copy in File]@row = 1, IF(AND(Expired@row = 0, [At Risk]@row = 0, Current@row = 0), "Green", IF(Expired@row = 1, "Red", IF([At Risk]@row = 1, "Yellow", IF(Current@row = 1, "Green", "")))), "Red") but this should really only be the way the health of the child rows is calculated.

I need to use a different formula for the parent rows which will use the following criteria:

1) If there are any child rows that are red, the parent row health = red

2) If there are more than 2 yellow children rows, the parent row health = red, if 2 or less = yellow

3) If there are no yellows, parent row health = green

Is there a way to have both types of calculations of health existing in the same column?

Right now all the parent rows are defaulting to red b/c they fail the child row criteria. If looking at the screenshot, in the scenario I'm looking for, the "Credentials" row would turn yellow, and the "Annual Hospital Compliance" row would turn green. The overall profile for Michelle Gonzalez would still be red


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!