Q: Excel - Conditional value if all cells in range equal a specific string Apart from a nested IF statement, is there a more elegant way of accomplishing it?
The idea is that if the values in Columns A-D are "Green", the Overall column says "Green" but only if all four (Q1-Q4) are Green. If there's at least one Amber in one of the Qs, the Overall should be Amber.
I know I could do a long IF statement but was hoping there will be a shorter way. We're using O365 and the spreadsheet will end up on a share point.
EDIT: Apologies. I should have mentioned that there can be a couple of other values apart from Amber. So it's really between either All are Green or NOT all are Green.
Cells are:


A: Try below formula.
=IF(COUNTIF(A2:D2,"Amber")>0,"Amber","Green")


This will also work
=IF(ISNUMBER(SUMPRODUCT(A2:D2="Amber")),"Amber","Green")

