Count of unique values in a list

  • Thread starter Thread starter uncmello
  • Start date Start date
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?
 
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.
 
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.
 
Is there a way to have the answer change when I use the filters? The
Subtotal function does this and is very usefull.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting unique values in column 3
Sum and count of unique values 4
Data Validation - List of unique values 2
Count Unique Values from a Subset of a List 5
Counting the number of unique values in a filtered list 5
Counting unique values 1
Counting unique values with criteria 4
Conditional count of unique values 5

Back
Top