RAG Formula help

edited 02/19/24 in Formulas and Functions

I am trying to get my formula to work for an overall RAG based off five RAG statuses.

I have got the following correct:

If 1 Red and 1 amber shows amber

If 2 + Reds show Red

If 1 amber + 4 greens shows Green.

However I also need it to be if it is 1 Red and the other 4 are green it should show amber and it is currently showing as red. Please can anyone help with what I need to do?

=IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 1, "Red", IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 0, IF(COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 0, "Yellow", "Red"), IF(COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 1, "Yellow", "Green")))


  • KPH
    KPH ✭✭✭✭✭✭

    I think we can simplify this a little to make it easier to edit. If you want to show as Green if there are 4 greens, regardless of whether the other one is red or yellow you could do that in one step.

    The logic is:

    If count Red greater than one then "Red"

    --- If false, if count Red greater than 0 and count yellow greater than zero, then "Yellow"

    ------ If false, if count Green greater than 4, then "Green"

    --------- If false, Blue*

    * I like to use the 4 color symbols in the formula column so I can capture any rows that do not meet my logic. A blue row will be more obvious and make troubleshooting easier.

    This formula will do the above

    =IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 1, "Red", IF(AND(COUNTIF(CAPEX@row:Timeline@row, "Red") > 0, (COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 0)), "Yellow", IF(COUNTIF(CAPEX@row:Timeline@row, "Green") > 3, "Green", "Blue")))

    Let me know how you get on.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!