Count of unique values in a list

U

uncmello

I need the total number of unique values in a list. I don't what to know how
many of each value there is.
Here's an example
33071
33071
33071
33072
33683
33912
33912
33912
33953
34250
In this case I need the answer to be 6
I also would like it to update the answer when I change the filter. Is that
possible?
 
S

ShaneDevenshire

Hi

Try:

=SUM(1/COUNTIF(Range,Range))

Enter it as an array by pressing Shift+Ctrl+Enter

or

=SUMPRODUCT(1/COUNTIF(Range,Range))

Range is just the range where your items are.
 
S

Sheeloo

Use the following ARRAY formula (will work only wth numbers/blank cells);
=SUM(N(FREQUENCY(A1:A10,A1:A10)>0))

Press CTRL and SHIFT keys while pressing ENTER after entering the above
formula.

assuming your data is in the range A1:A10

This is taken from http://www.cpearson.com/Excel/Duplicates.aspx
You may like to read it for future use.
 
U

uncmello

Is there a way to have the answer change when I use the filters? The
Subtotal function does this and is very usefull.
 
Top