# Count number of duplicate records.

1. ## Count number of duplicate records.

Hello,

My data is as follows:

Column A
1. Apple
2. Banana
3. Apple
4. Orange
5. Apple
6. Banana
7. Chickoo.

I want a formula which gives result as 2 as only 2 items apple & banana is duplicated irrespective of their number of occurances.

Thank you.
Vivek.

2. ## Re: Count number of duplicate records.

=sum(if(frequency(match(a1:a7,a1:a7,),match(a1:a7,a1:a7,))>1,1)). Cse

3. ## Re: Count number of duplicate records.

=SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$20<>"",MATCH(\$A\$2:\$A\$20,\$A\$2:\$A\$20,0)),ROW(\$A\$2:\$A\$20)-ROW(\$A\$2)+1)>1,1))
and enter as an array - using Control+shift+enter

ignore blanks - hence range can be more than the data you have

4. ## Re: Count number of duplicate records.

Or with Ctrl+Shift+Enter

=SUM(IFERROR((COUNTIF(A1:A99,A1:A99)>1)/COUNTIF(A1:A99,A1:A99),))

5. ## Re: Count number of duplicate records.

Thanks to all.

Additionally, I also want to find duplicate values i.e. apple & banana in 2 cells one below the other.

6. ## Re: Count number of duplicate records.

can you give an example

7. ## Re: Count number of duplicate records.

I want a formula in cell B1 to give apple & when I copy it down to B2 it should give banana as only these two are repeated items.

8. ## Re: Count number of duplicate records.

Try in B1 fill down until you get blanks.
Formula:
`Please Login or Register  to view this content.`

9. ## Re: Count number of duplicate records.

try in b2
=IFERROR(INDEX(A2:A15, MATCH(0, COUNTIF(B1:\$B\$1, A2:A15)+IF(COUNTIF(A2:A15, A2:A15)>1, 0, 1), 0)), "")

USE control + shift + enter

10. ## Re: Count number of duplicate records.

Solved..thanks a lot..

11. ## Re: Count number of duplicate records.

you are welcome

There are currently 1 users browsing this thread. (0 members and 1 guests)