annie wrote:
I want to count the unique values, but no need to specify a certain
value.Please see the columns below:
c1 c2
a a
b b
a a
a a
a a
b c

There are 2 unique values in this c1 (a,b),so i want the result to be 2.In
c2,the result should be 3,because there are 3 unique values(a,b,c). I need a
formula which can count how many unique values appear in a column. Since I
don't know which value will appear in the column, I can not specify the exact
value neither use the COUNTIF formula.
Thanks for your help:)

=SUMPRODUCT((Range<"")/COUNTIF(Range,Range&""))

If you have the morefunc.xll add-in...

=COUNTDIFF(Range,FALSE,"")