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

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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", .............

• ✭✭✭✭
Options

Thank you!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!