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

BB2791

Hello,

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

Paul Newcome

You would first need to create the nested IFs for the parent rows (typically it would be the logic of if the COUNTIFS of CHILDREN that are "Red" is greater than or equal to 1, "Red", then same for yellow then green for everything else).

Then you would nest the two together in another IF to say that IF the COUNT of CHILDREN is zero, output parent row formula, otherwise output child row formula.

=IF(COUNT(CHILDREN()) = 0, parent_row_formula, child_row_formula)

BB2791

Thanks @Paul Newcome. Would you be able to show me the structure of the parent row formula with the COUNTIFS? I'm not very knowledgeable when it comes to creating formulas.

Paul Newcome

It would be a nested IF. Here is the logic for the "Red". You would use the same logic for "Yellow" second, and then just make "Green" the "value if false".

=IF(COUNTIFS(CHILDREN(), "Red")> 0, "Red", .............

BB2791

Thank you!

Paul Newcome

Happy to help. 👍️