From the OPs post there was a full set of data, so in his case there was no
need to handle blanks.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Peo Sjoblom" wrote in message
...
I would personally use the variant

=SUMPRODUCT(--(A1:A1000<""),1/COUNTIF(A1:A1000,A1:A1000&""))

otherwise you'll get DIV/0 errors if there are blank cells, it works as
follows

the 1/countif part returns an array of numbers, if there is one value
unique
it will return 1,
if there are 2 values that are the same it will return 2 times 0.5 (1/2 =
0.5), if 3 it will return 0.333333, 4 0.25 and so on

assume we have this in A1:A10

1
2
3
4
65
6
1
2
3
4


it would be 6 unique values, the 1/countif returns

{0.5;0.5;0.5;0.5;1;1;0.5;0.5;0.5;0.5}

sumproduct will sum them to return 6, if we change the last number 4 to 1
so
there would be 3 1


{0.333333333333333;0.5;0.5;1;1;1;0.333333333333333 ;0.5;0.5;0.333333333333333
}

still returns the total of 6

I believe former MVP Dave Hager was the originator of it although it has
been converted from

=SUM(1/COUNTIF))

to sumproduct thus it can be entered normally

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"Bill Kuunders" wrote in message
...
Bob, I check this news group frequently as a means to learn stuff.

Could you please explain why and how your formula works?

Thank You
--
Greetings from New Zealand
Bill K



"Bob Phillips" wrote in message
...
=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"RJL0323" wrote
in
message ...

Hello All,
I have a question related to counting unique values in a column of
data. I will try to illustrate my question. I have a column of data
with 1000 rows. In this column there are duplicated values. I would
like to be able to use a function count how many unique values are in
the column. Let's say there were 4 duplicates of 250 values in the
column. I would like to be able to write a function to calculate the
250. I am very familiar with Excel and am able to acheive the number
through subtotals and/or pivot tables. I know I can find how many
instances one specific value appears in the column through sumproduct
and/or countif statements, but the function to calculate the number of
unique values has really got me stumped.

Does anyone have any ideas?

Thanks in advance!!
RJ


--
RJL0323

------------------------------------------------------------------------
RJL0323's Profile:
http://www.excelforum.com/member.php...o&userid=19456
View this thread:
http://www.excelforum.com/showthread...hreadid=513331