IF AND statement with cell range
Glad to help!
The two dashes are a double negative, the equivalent of multiplying by -1
twice. The SUMPRODUCT function only works with numbers. The statement
A1:A1000=C1 returns either TRUE or FALSE (not numbers). In order to get this
to work in the SUMPRODUCT, we need to convert TRUE/FALSE to numbers. Excel
considers TRUE to be equal to 1 and FALSE to be equal to 0. By multiplying
by -1, we get -1 or 0. Then multiply by -1 again, we get 1 or 0. Basically,
what Excel already knows, but now the SUMPRODUCT function can utilize the
results.
HTH,
Elkar
"JN" wrote:
I am an idiot and saw my problem. Your solution works perfectly and I truly
appreciate your help!!! Question: What do the two dashes mean?
"Elkar" wrote:
See if this works for you:
=IF(SUMPRODUCT(--($A$1:$A$1000=C1),--($B$1:$B$1000=D1))0,"YES","NO")
Note that ranges used in the SUMPRODUCT function cannot be entire columns,
such as $A:$A. You could use $A$1:$A$65535.
HTH,
Elkar
"JN" wrote:
I am trying to compare 2 cells against a range to determine if there is a
match:
Col A Col B Col C Col D
MARY BROWN TED SMITH
TED SMITH
BOB JONES
I'm using =IF(AND(C3=$A:$A,D3=$B:$B),"YES","NO") but it's obviously wrong
because I'm getting "NO" when it should return "YES". What am I doing wrong?
|