I am building a project tracking excel workbook that has three columns (A, B and C) that display either "RED", "YELLOW" or "GREEN" stoplight chart (the color is a selectable value from a data list, so it actually says "RED" in the cell). I need to create a forth column (D) that will display an "Overall" project status based upon the contents of A, B and C. See the attached file for more details.
The rules for selecting the value are as follows:
If ALL cells are "GREEN" then the overall health of the project should show "GREEN"
If any one of the cells is "RED" then the overall health of the project is "RED"
If any one of the cells is "YELLOW and no other cells are "RED" then the overall project health is "YELLOW"
I have tried IF statements, OR statements and even SUMPRODUCT statements, but can seem to get it to work.