# count number of duplicates between 2 columns

S

#### SteveC

Col A Col B
apples apples
oranges pears
pears bananas
pineapplies coconuts
grapes
raisins

Count the number of times that data in column A matches the data in column B

the formula in this case would return: 2

thanks

S

#### Sheeloo

Enter this in C1 against apples in A1
=COUNTIF(\$B\$1:\$B\$6,A1)
and copy down

S

#### SteveC

Thanks... but hoping for a single formula with no helper columns... thanks...

S

#### Sheeloo

No problme.

Use
=COUNTIF(\$B\$1:\$B\$6,B1)
assuming you have values in Col B.

S

#### SteveC

oh, and one more thing, don't count number of matching blank cells

S

#### Sheeloo

I use
=COUNTIF(\$B\$1:B1,B1)

This give me 1 against the first occurrence, 2 against the second and so
on... I can then retain the rows with 1 and delete others.

S

#### SteveC

That would do it if I was only worried about Column B, but I want to compare
it against Column A. I just posted a new question with more details about
the problem. Thanks for taking the time to help.

R

#### Roger Govier

Hi Steve

Try
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A6,B1:B6,0))))

T

#### T. Valko

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4,B1:B6,0))))