Red, Yellow, Green multiple columns



I'm trying to create a formula that will put out a color according to multiple colors in a column. For example: there are columns 1 thru 6 that are rated red, yellow or green. In column 7, if any are yellow, it will output a yellow and if any are red, will output a red dot; if all are green, will output a green dot. I know how to do it for yellow and green, not sure how to add red to the equation. This is what I have so far:

=IF(OR([Column1]@row = "Yellow", [Column2]@row = "Yellow", [Column3]@row = "Yellow", [Column4]@row = "Yellow", [Column5]@row = "Yellow", [Column6]@row = "Yellow"), "Yellow", "Green")


Best Answer

  • heyjay
    heyjay ✭✭✭✭✭
    Answer ✓
    =IF(COUNTIF(A@row:C@row, "Red") > 0, "Red", 
    IF(COUNTIF(A@row:C@row, "Yellow") > 0, "Yellow", 

    Assuming that your status columns are side by side, replace A@row:C@row with the first and last column. eg Column1@row:Column6@row



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!