Compare Range of Cells with another Range of Cells

[immortali]

Hi,

I want to compare a range of cells with another range and am given how many of the cells correspond horizontally to the other range of cells.

If for example I have this

ABC ABC
DEG DFG
DEF DEF
DFA DFB

It would return that I have 2 since only 2 lines are like each other....

How can I do this in excel?

 

[Weazel]

something like....

=SUMPRODUCT(--(A1:A4=B1:B4))

 

[immortali]

Unfortunately that counts also cells that are not in line with the other values (horizontally) So if I have ABC in A3 and ABC in B4 it would still count it

something like....

=SUMPRODUCT(--(A1:A4=B1:B4))

 

[immortali]

My bad.... However when nothing compares it shows 6.... would it mean that if I do -6 it works?

 

[Weazel]

if nothing compares you should be getting a 0

do you have any blanks selected in both ranges?

They would compare as = and add to the count also