count matching numbers

[Catfish]

This is probably simple but I'm about to go crazy trying to figure it
out. Please help.
I'm trying to count or total how many cells match another row of
cells. For instance
A4 = 23 B4 = 14
A5 = 34 B5 = 30
A6 = 39 B6 = 34
A7 = 48 B7 = 39
A8 = 53 B8 = 55

The answer I'm looking for is 2 because there are two cells that match.
(A5 matches B6 and A6 matches B7)

Thanks,
mike

 

[T. Valko]

Try ths:

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A8,B4:B8,0))))

 

[Catfish]

Try ths:

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A8,B4:B8,0))))

--
Biff
Microsoft Excel MVP








- Show quoted text -

Worked like a charm!

Thank you!!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Try ths:

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A8,B4:B8,0))))

--
Biff
Microsoft Excel MVP








- Show quoted text -

Worked like a charm!

Thank you!!

 

[Bernd P]

Hello,

Another solution:
Array-enter (with CTRL + SHIFT + ENTER, not only with ENTER)
=SUM(--(A4:A8=TRANSPOSE(B4:B8)))

If the tables are of size 5000 then this formula is about 380x faster
(runtime observed with FastExcel).

Regards,
Bernd