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

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

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

HTH,
Paul



--

"Not Excelerated" wrote in
message ...
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

Unfortunately that does not work. Other ideas??

"PCLIVE" wrote:

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

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

HTH,
Paul



--

"Not Excelerated" wrote in
message ...
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

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

"Not Excelerated" wrote:

Unfortunately that does not work. Other ideas??

"PCLIVE" wrote:

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

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

HTH,
Paul

Sure does. You did change to suit your ranges?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Not Excelerated" wrote in
message ...
Unfortunately that does not work. Other ideas??

"PCLIVE" wrote:

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

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

HTH,
Paul



--

"Not Excelerated" wrote in
message ...
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

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

"Dave" wrote:

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

"Not Excelerated" wrote:

Unfortunately that does not work. Other ideas??

"PCLIVE" wrote:

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

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

HTH,
Paul

Hi,
Glad you got it to work.
Dave.