How to display a single value from an array of cell values based on rules


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.  



Selected Answer

Reply: Try putting in formula in F2: =IF(AND(A2="GREEN",B2="GREEN",C2="GREEN"),"GREEN",IF(OR(A2="RED",B2="RED",C2="RED"),"RED","YELLOW"))

Note: the AND formula for the GREEN and the others are OR.



That's it!  I wasn't adding the "YELLOW" as the False answer for the second IF statement.  Thanks so much!
Johnkl15 (rep: 2) Jul 19, '17 at 1:47 pm
Excellent.  If I helped you, please Give credit. Thx
queue (rep: 348) Jul 19, '17 at 1:48 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login