Find one or the other or both values in range, different result for each-CodePudding

I want to automatically update a text based on the values found in a range (Sheet=information 36:36). If the range only contains "red" then the result should be "red text", if the range only contains blue, the result should be "blue text". However if both values are found the result should be "purple text"

I can make it get one or the other but I cannot get the desired result when both values are found. I tried some variations of COUNTIF and SUMPRODUCT but the formula below best shows what I want ( even though this formula doesn't work)

=IF(COUNTIF([Information!36:36,="blue", "blue text",IF(COUNTIF(Information!36:36,="red","red text",IF(COUNTIF(Information!36:36, ="red",Information!36:36,="blue", "purple text"))

This shows what I want to get. If there is at least one "red" the result =red, at least one blue=blue, at least one of both=purple

Could someone perhaps help me with the formula?

CodePudding user response：

Well this works:

=IF(AND(COUNTIF(2:2,"red")>=1,COUNTIF(2:2,"blue")>=1),"Purple",IF(COUNTIF(2:2,"red")>=1,"Red",IF(COUNTIF(2:2,"blue")>=1,"Blue")))

Note, I have not changed the image though.

The testing logic and values are important.