Comparing 2 Columns using SUMPRODUCT ONLY

[meyero90]

Hello Gurus,

This should be an easy one. I used to know how to use this SUMPRODUCT function to compare two columns but it seems not to work...I might have forgotten how to do it
In A1:A5, I have letters A,B,C,D,E
In B1:B5, I have letters D,E,F,N,A
--> I tried

Please Login or Register  to view this content.

But unsuccessful result.

I know it works using

Please Login or Register  to view this content.

, and returns TRUE,FALSE with respect to the missing lookup_value.

The Above SUMPRODUCT() should return as I remember 1 or 0.

Cheers guys

[NBVC]

You need the double unary in front of each conditional argument:

=SUMPRODUCT(--($A$1:$A$5=$A1),--($B$1:$B$5=$B1))

if you are going to sum another array based on those conditions, then the summing array does not take the double unary

=SUMPRODUCT(--($A$1:$A$5=$A1),--($B$1:$B$5=$B1),$C$1:$C$5)

[contaminated]

Rewriting ISNA / MATCH with SUMPRODUCT..
=SUMPRODUCT((A1=$B$1:$B$5)*1)

[meyero90]

Contaminated solution works....thanks but I want to make the sumproduct work.
NBVC, when I use your solution it returns 1 to every match which is incorrect since A2 and A3 are not in B1:B5. Is there a way you can fix that?

[shg]

If you want to know if all of the entries in A1:A5 are in B1:B5,

=AND(ISNUMBER(MATCH(A1:A5, B1:B5, 0)))

... array-entered.

[NBVC]

Or how many of A1:A5 are in B1:B5:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A5,B1:B5,0)))

[meyero90]

thanks guys for your help. much appreciated!