IF OR statement using multiple cells - Confused

[easybpw]

Hello,

I'm trying to construct a formula that basically does this. I'll keep it simple.

If cell A1 has "x" then G1 = "Blue" but if cell A1 and B1 both have "x" then G1 = "Blue Green" but if cell A1, B1, and C1 have "x" then G1 = "Blue Green Red". How can I do this? It' confused me for the last hour.

Thanks!

Bill

 

[JumboCactuar]

Try

=IF(AND(A1="x",B1="x",C1="x"),G1="Blue Green Red",IF(AND(A1="x",B1="x"),G1="Blue Green",IF(A1="x",G1="Blue",""))

 

[jtakw]

Hi,

What happens when A1 and C1 is "x", or B1 and C1 is "x" ?

Otherwise, just take out the parts G1= from above formula by JumboCactuar.

 

[easybpw]

Hi,

Thanks JTAKW, I forgot about the other scenarios. Yes, if A1 & C1 = "x" then H1 would be "Blue Red", B1 & C1 = "x" H1 = "Green Red". I am thinking this is probably not going to be possible but I hope I'm wrong.

 

[JumboCactuar]

this what you need? place in G1

=IF(AND(A1="x",B1="x",C1="x"),"Blue Green Red",IF(AND(A1="x",B1="x",C1<>"x"),"Blue Green",IF(AND(A1<>"x",B1="x",C1="x"),"Green Red",IF(AND(A1="x",B1<>"x",C1="x"),"Blue Red",IF(AND(A1="x",B1<>"x",C1<>"x"),"Blue",IF(AND(A1<>"x",B1="x",C1<>"x"),"Green",IF(AND(A1<>"x",B1<>"x",C1="x"),"Red","")))))))

messy but works, not sure if theres a cleaner way of doing this

 

[jtakw]

Hi,

Thanks JTAKW, I forgot about the other scenarios. Yes, if A1 & C1 = "x" then H1 would be "Blue Red", B1 & C1 = "x" H1 = "Green Red". I am thinking this is probably not going to be possible but I hope I'm wrong.

No problem at all, it's actually very do-able, but do you want anything returned if Only B1 is X, or Only C1 is X?

 

[jtakw]

messy but works, not sure if theres a cleaner way of doing this

Yes there is, waiting for OP's reply on my question above.

 

[jtakw]

Well, seems OP has gone off-line, so I'll post my formula anyway.

As it is If None of A1, B1, C1 is X, or Only B1, or Only C1 is X, returns Blank, otherwise, as per OP's requirements:

Book1
	A	B	C	D	H
1	x		x		Blue Red
Sheet223

Cell Formulas
Range	Formula
H1	=IFERROR(CHOOSE(SUM(IF(A1="X",1,0),IF(B1="X",2,0),IF(C1="X",4,0)),"Blue","","Blue Green","","Blue Red","Green Red","Blue Green Red"),"")

 

[jtakw]

And, if following OP's logic on the three different Colors, Blue for A, Green for B, and Red for C.
This covers all 8 possibilities (including all blank):

Book1
	A	B	C	D	H
1		x			Green
Sheet223

Cell Formulas
Range	Formula
H1	=IFERROR(CHOOSE(SUM(IF(A1="X",1,0),IF(B1="X",2,0),IF(C1="X",4,0)),"Blue","Green","Blue Green","Red","Blue Red","Green Red","Blue Green Red"),"")