Count unique entries

[slinkyjib]

Good afternoon,

I'm trying to count unique entries in a column that contains duplicates. The problem with this is, I can't use a pivot table ( spreadsheet is already huge), and I can't play with the data, so I need a formula to do it for me.

Below is column A. This is an extract of the data I am trying to count unique entries in.e.g. for the data below the count result should be 4.
3182
3182
3182
3186
82089
82089
82089
82089
5986
5986
5986
5986


Thanks in advance for any help.

 

[Domenic]

Try...

=SUMPRODUCT((A1:A12<>"")/COUNTIF(A1:A12,A1:A12&""))

Hope this helps!

 

[slinkyjib]

thanks for the reply. Looks good, but excel tells me it has a circular referene, and so doesn't like it.

 

[Domenic]

Can you post the exact formula you're using, along with the location of the formula?

 

[slinkyjib]

lol

you know what I did don't you......

Well it works fine now.

Thanks for your help.