# Formula for summarized health indicators for all rows

✭✭✭

We decided to use the formula below to auto color the status for each task Now we are trying to come up with a formula that would auto-calculate all of the row's statuses (Green, Red, Yellow) and display an Overall Health color - I assume it would summarize the colors and display that. Is that possible?

=IF(ISBLANK([End Date]@row), "", IF(AND([End Date]@row <= TODAY(), [% Complete]@row < 1), "Red", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(), [% Complete]@row < 0.25), "Yellow", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(), [% Complete]@row >= 0.25), "Green", IF([% Complete]@row = 1, "Green", IF(AND([Start Date]@row >= TODAY(), [End Date]@row >= TODAY()), "Green", ""))))))

• ✭✭✭✭✭✭
edited 04/18/22

I would add a helper column to Score each color then hide it, then for the Overall Health, use the average values

Score =IF(Health@row = "Red", 0, IF(Health@row = "Yellow", 1, IF(Health@row = "Green", 2)))

Overall Health =IF(AVG(Score:Score) < 0.75, "Red", IF(AVG(Score:Score) < 1.50, "Yellow", IF(AVG(Score:Score) < 2, "Green")))

• ✭✭✭

Thank you very much for your help.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!