count number of duplicates between 2 columns

[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

 

[Sheeloo]

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

 

[SteveC]

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

 

[Sheeloo]

No problme.

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

 

[SteveC]

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

 

[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.

 

[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.

 

[Roger Govier]

Hi Steve

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

 

[T. Valko]

Try this:

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