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

bhuyler

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

Genevieve P.

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

bhuyler

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

Genevieve P.

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

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!

Dave Charboneau

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

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