Count Dups in Two Columns

[Not Excelerated]

Hello!

I have two sets of data in Columns A and B. I want to find out how many
duplicates there are between column A and column B. I just want the know the
total number of duplicates without copying down in another column. In the
example below, I want to post the formula in cell A9 to get the number of
duplicates...in this case, it's 3. Thanks in advance!!!

A B
1 4563 1231
2 1231 1551
3 6598 4563
4 2681 2681
5 32648 165165
6 131 156
7 416516 12
8
9 3
10

 

[PCLIVE]

Not sure if this will have flaws, but one way that seems to work:

=SUMPRODUCT(--(COUNTIF(B1:B7,C1:C7)))

HTH,
Paul

 

[Not Excelerated]

Unfortunately that does not work. Other ideas??

Not sure if this will have flaws, but one way that seems to work:

=SUMPRODUCT(--(COUNTIF(B1:B7,C1:C7)))

HTH,
Paul

 

[Dave]

Hi,
Works for me on your data, if you correct the references:
=SUMPRODUCT(--(COUNTIF(A1:A10,B1:B10)))
Regards - Dave

 

[Don Guillett]

Sure does. You did change to suit your ranges?

 

[Not Excelerated]

I stand corrected and again in humility...thanks to both of you Paul and Dave!
Chris

 

[Dave]

Hi,
Glad you got it to work.
Dave.