Formula to capture overall health of projects on one sheet

Brittaney Pizzato

Hello,

I'm looking for a formula to capture in the Portfolio Health when the Overall Health status changes.

I would like the formula in the Portfolio Health cell to show Green when all Overall Health cells are Green. When one Overall Health cell changes to Yellow, the Portfolio Health turns Yellow, and when there are 2+ Yellows or 1 Red cell, the Portfolio Health turns Red.

Can you help? Below is the Metric I use to find my Portfolio Health:

All Green cells = Green Portfolio Health

1 Yellow cell = Yellow Portfolio Health

2+ Yellow cells = Red Portfolio Health

1+ Red cell = Red Portfolio Health

Thank you!!

SteyJ

Try this:

=IF(COUNTIF([Overall Health]:[Overall Health], "Yellow") = 1, "Yellow", 
IF(COUNTIF([Overall Health]:[Overall Health], "Yellow") >= 2, "Red", 
IF(COUNTIF([Overall Health]:[Overall Health], "Red") >= 1, "Red", "Green")))