Conditional Help

[JayReina13]

Need help


so i have cells b2, C2, e2, f2, g2, that have different numbers and I want to check


if any of these cells numbers match cell A1 ( c2=3 and A1 = 3) then AA1 = 1, else leave blank

How can i do that ?


Thnaks in advance Jay

 

[Sal Paradise]

In AA1:
=IF(--SUM(B2=A1,C2=A1,E2=A2,F2=A1,G2=A1)>0,1,"")

B2=A1, etc. will all give a "true" or "false" answer. TRUE = 1, FALSE = 0, so when you add them up, if the answer is anything other than zero, you have a match. So if the answer is greater than 0, enter a 1. Otherwise, enter a blank ("").

 

[T. Valko]

In AA1:
=IF(--SUM(B2=A1,C2=A1,E2=A2,F2=A1,G2=A1)>0,1,"")

B2=A1, etc. will all give a "true" or "false" answer. TRUE = 1, FALSE = 0, so when you add them up, if the answer is anything other than zero, you have a match. So if the answer is greater than 0, enter a 1. Otherwise, enter a blank ("").

You can reduce that to:

=IF(SUM(B2=A1,C2=A1,E2=A1,F2=A1,G2=A1),1,"")

 

[Sal Paradise]

I didn't know if SUM would properly turn it into a number (hence the --). Learn something new every day.

 

[T. Valko]

I didn't know if SUM would properly turn it into a number (hence the --). Learn something new every day.

Excel is kind of quirky like that.

Consider this...

Book1
	B	C
2	TRUE	0
3	TRUE	0
4	TRUE	4
5	TRUE	_
Sheet1

Formulas:

C2: =SUM(B2:B5)
C3: =SUM(B2,B3,B4,B5)
C4: =SUM(B2=TRUE,B3=TRUE,B4=TRUE,B5=TRUE)

The logical tests in C4 all evaluate to TRUE as do the referenced values in the other formulas yet the formula in C4 is the only one that returns the expected count.

 

[Sal Paradise]

I knew there was something fishy about that =SUM() character. I never really trusted him. I always thought he was in cahoots with =VLOOKUP() to do something dastardly when he thought nobody was watching.

 

[T. Valko]

I knew there was something fishy about that =SUM() character. I never really trusted him. I always thought he was in cahoots with =VLOOKUP() to do something dastardly when he thought nobody was watching.