Skip to content
  • Try for free

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Help with Conditional Formatting for "Overall Health" Column

    ✭✭
    edited 03/31/25 in Formulas and Functions

    Hi everyone,

    I need help modifying my "Overall Health" column to account for more than two different colors in a row. Right now, my logic works as follows:

    • If all values are green, the overall health is green.
    • If there is at least one yellow and the rest are green, the overall health is yellow.
    • If there is at least one red, the overall health is red.

    Now, I need to add these additional conditions:

    1. If there is at least one red and one yellow and/or green, the overall health should be red.
    2. If there is any gray, the overall health should be gray (overriding other colors).

    How can I implement this logic correctly? Any help would be greatly appreciated!

    Thanks in advance!

    · Share on FacebookShare on Twitter

    Best Answer

    • Community Champion
      Answer ✓

      You have already solved new condition 1. If there are more than 0 reds then it will be red.

      For condition 2, you need to move the grey logic to the start of the formula (the order is what allows it to override). If there is more than 0 gray, return gray (overriding all other colors).

      If not, then it will look at red, if there is more than 0 red, then return rend.

      If no reds, then it will look at yellow, and return yellow if there is atleast 1 yellow, and if none of those things are true, it will return green.

      =IF(COUNTIF(Resources@row:Budget@row, "Gray") > 0, "Gray", IF(COUNTIF(Resources@row:Budget@row, "Red") > 0, "Red", IF(COUNTIF(Resources@row:Budget@row, "Yellow") > 0, "Yellow", "Green")))

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      You have already solved new condition 1. If there are more than 0 reds then it will be red.

      For condition 2, you need to move the grey logic to the start of the formula (the order is what allows it to override). If there is more than 0 gray, return gray (overriding all other colors).

      If not, then it will look at red, if there is more than 0 red, then return rend.

      If no reds, then it will look at yellow, and return yellow if there is atleast 1 yellow, and if none of those things are true, it will return green.

      =IF(COUNTIF(Resources@row:Budget@row, "Gray") > 0, "Gray", IF(COUNTIF(Resources@row:Budget@row, "Red") > 0, "Red", IF(COUNTIF(Resources@row:Budget@row, "Yellow") > 0, "Yellow", "Green")))

      · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions