Updating Overall Status based on 3 other Statuses


Hello, I need help writing a formula for the Overall Health cell based on the Scope Health, Budget Health, and Schedule Health cells.

If any of the 3 cells are yellow, Overall is yellow. If any of the 3 cells are red, Overall is red. If all 3 are green, overall is green.

Best Answer

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓

    Hi @Bvinston

    Please see if this will work for you:

    =IF(CONTAINS("Red", [Scope Health]@row:[Schedule Health]@row), "Red", IF(CONTAINS("Yellow", [Scope Health]@row:[Schedule Health]@row), "Yellow", IF(CONTAINS("green", [Scope Health]@row:[Schedule Health]@row), "Green", "")))

    Hope this helps!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!