Symbols

Hi I am trying to add up colour symbols to return another colour symbol.

Please could someone help with the formula.

For example

if 3 greens = green

If 2 green, 1 yellow = Yellow

If 3 reds or 2 yellows and a red = Red

Regards

John

Answers

  • John,

    This may not be a perfect answer, but hope it points you in the right direction for your application. You may need to tweak the rules within the formula slightly or add an additional IF statement.

    See image attached. The formula I used in "Column 8" as a "total" was:

    =IF(COUNTIF([Column5]@row:[Column7]@row, "Green") = 3, "Green", IF(COUNTIF([Column5]@row:[Column7]@row, "Red") >= 1, "Red", "Yellow"))

    So, 3 Greens = Green; 1 or more Reds = Red; Anything Else = Yellow.


  • Thanks Kevin

    Every Thing works apart from the last formula.

    If any of the symbols are Red then return a Red symbol.

    =IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Green") = 3, "Green", IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Green") = 2, "Yellow", IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Green") = 1, "Red", IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Red") = >1, "Red"))))


    Thanks for your help.

    Regards

    John

  • =IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Green") = 3, "Green", IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Green") = 2, "Yellow", IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Green") = 1, "Red", IF(COUNTIF([Door Leaf 1]@row:[Door Leaf 3]@row, "Red") >= 1, "Red"))))


    Check your "greater than" part. I have bolded an update that I think should help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!