Count of unique values in a list
 

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?

Count of unique values in a list
 

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.


--
Cheers,
Shane Devenshire


"uncmello" wrote:

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?

Count of unique values in a list
 

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.
"uncmello" wrote:

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?

Count of unique values in a list
 

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

"ShaneDevenshire" wrote:

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.


--
Cheers,
Shane Devenshire


"uncmello" wrote:

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?