Count Dups in Two Columns

N

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
 
P

PCLIVE

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

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

HTH,
Paul
 
N

Not Excelerated

Unfortunately that does not work. Other ideas??

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

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

HTH,
Paul
Click to expand...
 
D

Dave

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

Not Excelerated

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Total Number of Dups in 2 columns 1
Two columns followed by single page-width column on same page 0
Compare identifier columns from 2 sheets - remove rows that are dups 2
Formating bolded text exclusively. 0
Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Two columns : Continuous two columns vs Parallel two Column 1
Setting column width in a table with a macro is not working 0
Finding the right style field in VBA 0

Top