Overall RYG Health Formula Based on Other Combined RYG Columns

Smartsheet Community:

Does Anyone Have a RYG Formula that Looks at 3 Other RYG Columns to get the Over Health I Could Leverage?

For Example:

Scenario 1: If All 3 Columns are Green, then Overall Health is Green

Scenario 2: If All 3 Columns are Red, Then Overall Health Red

Scenario 3: If All 3 Columns are Yellow, Then Overall Health Yellow

Scenario 4: If All 2 Columns are Yellow and 1 is Green, Then Overall Health is Yellow

Here are is The Formula Column:

These are The Columns it is Referencing:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. Give this a go...

    =IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Red") = 1, "Yellow", IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Red") = 2, IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Green") = 1, "Yellow", "Red"), IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Red") = 3, "Red", IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Green")>= 2, "Green", "Yellow"))))

    (1) If there is only one "Red" then output "Yellow". This takes care of GGR / GYR / YYR.

    (2) If there are two "Red", if the third is "Green" then output "Yellow", otherwise output "Red". This takes care of GRR / YRR.

    (3) If there are 3 "Red", then output "Red". This takes care of RRR.

    (4) If there are two or more "Green" then output "Green". This takes care of GGG / GGY.

    (5) All else outputs "Yellow". This takes care of GYY / YYY.

    (4) GGG = G

    (4) GGY = G

    (5) GYY = Y

    (1) GGR = Y

    (2) GRR = Y

    (1) GYR = Y

    (5) YYY = Y

    (1) YYR = Y

    (2) YRR = R

    (3) RRR = R


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!