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
-
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 -
@Dave Charboneau thanks. The link appears to be broken to the training however.
Answers
-
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 -
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
-
@Dave Charboneau thanks. The link appears to be broken to the training however.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!