Nested IF (AND< OR, XOR) Problem

[Brampton76]

Good Evening,

I have a range of cells that are filled with text that is either Green, Amber or Red. If all the cells are Green then the answer is Green, if there is a mix of Amber and Green then the answer is Amber, if all the cells are Amber then the answer is Amber, if the cells are a mix of Amber and Red then the answer is Red and finally, if all the cells are Red then the answer is Red. I have toyed with different mixes of IF, AND, OR and then XOR but I am struggling to produce the correct answer and wondered if anyone could point me in the right direction please?

Kind Regards

 

[JoeMo]

This assumes your range is A1:A10 (10 cells), change the range to suit:

=IF(COUNTIF(A1:A10,"Red")>=1,"Red",IF(COUNTIF(A1:A10,"Amber")>=1,"Amber","Green"))

 

[BlueHornet]

I think JoeMo's answer will work fine if you're working on cells whose text is the WORDS "Amber", "Green" and "Red", but I wondered if you were trying to filter on text font color. In that case, I think VBA will be required or perhaps manually filtering on the text color, and then using the results of the filter condition to set your "answer" value.

 

[Brampton76]

Good Morning,

Many thanks for help, it works a treat.

Kind Regards