How to a build a formula to change Overall Project Health if Color codes in another column(s)

So I've been asked to create a forumla based solution that does changes the color code in 'Overall Project Health Status' column if any changes occur in the Scope, Schedule,Budget, Client Satisfaction columns (see above) using the following criteria (see above screen shot) for criteria. Not really good with formulas in Smartsheet, so any help I can get to not waste alot of time 'experimenting' would be great.

Thanks

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @bhuyler

    Try this and see if it suits all of your possible combinations:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red", IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow", "Green"))

    I'll break down each of the parts of the formula for you.

    Red Instruction

    You have two instances where the Overall could be "Red" - if there's even just one Red status ball anywhere in the row, OR if there's 4 yellow status balls.

    First we count how many Reds there are across the row:

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1

    then we count the Yellows

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4

    and we put that in an OR Function:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red"


    Yellow Instruction

    Next we're looking to see if there are 2 ore more yellows in the row, to then turn yellow. We don't have to specify what happens if there are 4 yellows, since we wrote that in the Red rule and the Red rule comes first.

    IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow",


    Green Rule

    Anything else will return Green.


    I will note that we didn't include anything in for if there are blank cells, so if there is only 1 Green status ball or 1 Yellow status ball and all the others are blank, it will return Green as the overall health.


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • bhuyler
    bhuyler ✭✭
    Answer ✓

    @Dave Charboneau thanks. The link appears to be broken to the training however.

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @bhuyler

    Try this and see if it suits all of your possible combinations:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red", IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow", "Green"))

    I'll break down each of the parts of the formula for you.

    Red Instruction

    You have two instances where the Overall could be "Red" - if there's even just one Red status ball anywhere in the row, OR if there's 4 yellow status balls.

    First we count how many Reds there are across the row:

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1

    then we count the Yellows

    COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4

    and we put that in an OR Function:

    =IF(OR(COUNTIF(Scope@row:[Client Satisfaction]@row, "Red") >= 1, COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") = 4), "Red"


    Yellow Instruction

    Next we're looking to see if there are 2 ore more yellows in the row, to then turn yellow. We don't have to specify what happens if there are 4 yellows, since we wrote that in the Red rule and the Red rule comes first.

    IF(COUNTIF(Scope@row:[Client Satisfaction]@row, "Yellow") >= 2, "Yellow",


    Green Rule

    Anything else will return Green.


    I will note that we didn't include anything in for if there are blank cells, so if there is only 1 Green status ball or 1 Yellow status ball and all the others are blank, it will return Green as the overall health.


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • bhuyler
    bhuyler ✭✭

    Hi Genevieve,


    Yes that was perfect. My colleague and I figured the IF arugment to find all reds, it was trying to figure out the COUNTIF logic that was giving me and her a hard time.


    Thank You!

  • This course in SmartSheet University is excellent when explaining how to set overall health for a project, activity or task based on criteria for it's children() ... https://smartu.smartsheet.com/series/core-features/taking-formulas-to-the-next-level

  • bhuyler
    bhuyler ✭✭
    Answer ✓

    @Dave Charboneau thanks. The link appears to be broken to the training however.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!