RAG Formula help
I am trying to get my formula to work for an overall RAG based off five RAG statuses.
I have got the following correct:
If 1 Red and 1 amber shows amber
If 2 + Reds show Red
If 1 amber + 4 greens shows Green.
However I also need it to be if it is 1 Red and the other 4 are green it should show amber and it is currently showing as red. Please can anyone help with what I need to do?
=IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 1, "Red", IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 0, IF(COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 0, "Yellow", "Red"), IF(COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 1, "Yellow", "Green")))
Answers

I think we can simplify this a little to make it easier to edit. If you want to show as Green if there are 4 greens, regardless of whether the other one is red or yellow you could do that in one step.
The logic is:
If count Red greater than one then "Red"
 If false, if count Red greater than 0 and count yellow greater than zero, then "Yellow"
 If false, if count Green greater than 4, then "Green"
 If false, Blue*
* I like to use the 4 color symbols in the formula column so I can capture any rows that do not meet my logic. A blue row will be more obvious and make troubleshooting easier.
This formula will do the above
=IF(COUNTIF(CAPEX@row:Timeline@row, "Red") > 1, "Red", IF(AND(COUNTIF(CAPEX@row:Timeline@row, "Red") > 0, (COUNTIF(CAPEX@row:Timeline@row, "Yellow") > 0)), "Yellow", IF(COUNTIF(CAPEX@row:Timeline@row, "Green") > 3, "Green", "Blue")))
Let me know how you get on.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 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!