count matching numbers

  • Thread starter Thread starter Catfish
  • Start date Start date
C

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
 
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 -
Click to expand...

Worked like a charm!

Thank you!!
 
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
 
Back
Top