# Count duplicate records

1. ## Count duplicate records

I want to be able to count the total number of unique records for my analysis.

However.... I cannot do this by simply highlighting the column and seeing what the cell count is because there are duplicates in my data.

column A has a list of id's eg below: There are 5 unique records not 10.

I want to be able to get this unique record count for my long list of data.

123
123
123
456
456
750
100
236
236
236

Is there a simple formula I can use for this???

2. ## Re: Formula to count dupplicate number of records in a cloumn

Hi stats,

depending on the data type:

if numeric

=SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1))

confirm with Ctrl-Shift-Enter

if mixed text and numbers

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""), IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))

again, confirm with Ctrl-Shift-Enter

3. ## Re: Formula to count dupplicate number of records in a cloumn

Yes exactly 1 instance of each entry.

Thanks so much....I will try that now and let you know if it worked. Cheers

I tried this and I thought I resolved it but no.....
=COUNTA(\$A:\$A)-1

4. ## Re: Count duplicate records

Thanks so much that first formula you provided worked.....but the ctr-shift-enter to confirm.......not so sure what was supposed to happen there????.......is it supposed to verify that my answer is correct?

=SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1))

5. ## Re: Count duplicate records

No, it's not about verification. The formula is an array formula. It evaluates not only individual cells, but arrays consisting of many cells. Array formulae must be confirmed with Ctrl-Shift-Enter. This will put curly brackets around the formula in the formula bar, so you can distinguish an array formula from a normal one. As soon as you edit the formula, the brackets no longer display and you must remember to hit Ctrl-Shift-Enter to save your formula changes.

6. ## Re: Count duplicate records

Originally Posted by teylyn
No, it's not about verification. The formula is an array formula. It evaluates not only individual cells, but arrays consisting of many cells. Array formulae must be confirmed with Ctrl-Shift-Enter. This will put curly brackets around the formula in the formula bar, so you can distinguish an array formula from a normal one. As soon as you edit the formula, the brackets no longer display and you must remember to hit Ctrl-Shift-Enter to save your formula changes.
Teylyn......

Thanks for that clarification..........Thanks again for your help..........this formula is invaluable...........I will find alot of use for this in my day to day work.

Cheers

7. ## Re: Count duplicate records

Teylyn,

One more question this formula =SUM(IF(FREQUENCY(A1:A10, A1:A10)>0,1)) does not seem to work in excel 2007....?

Thanks

8. ## Re: Count duplicate records

did you hit Ctrl-Shift-Enter or just Enter?

9. ## Re: Count duplicate records

Originally Posted by teylyn
did you hit Ctrl-Shift-Enter or just Enter?
Yep Teylyn,

That was it.......... I must have hit one wrong key when I did ctr-shft-ent....

Thanks a million....

GREAT HELP!!!!.................GREAT SITE!!!!!...............AWSOME FORMULA!!!

10. ## Re: Count duplicate records

If you prefer you can use SUMPRODUCT which (though not really any more efficient than the Array) does not require CTRL + SHIFT + ENTER entry - ie can be confirmed with Enter as normal.

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
confirmed with Enter

If using Arrays or SUMPRODUCT it is imperative you keep your ranges "lean".

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1