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???

Many thanks in advance.

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

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))

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

Teylyn,

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

Thanks

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!!!