Formula to capture overall health of projects on one sheet



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!!

Best Answer

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    Answer ✓

    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")))

    Jacob Stey


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!