Counting Unique Values

P

Paul Ferro

I have a constant situation where I need to count the unique account #'s in
a column. For example, I might have a column that looks like this:

123155
684768
684768
165465
068589
068589
068589
015646

There are 8 account numbers listed, but only 5 unique numbers. What I have
been doing thus far is sorting the column (for example column A) then
putting the following formula all the way down column B:

if(a2=a1,"",a2)
if(a3=a2,"",a3)
etc...

Then, I can count all of the values in column B and it will give me the
unique account #'s.

Is there a better way to do this?

Thanks,
Paul
 
G

Guest

If the data is in A1:A8, and there can be blank cells, try
=SUMPRODUCT((A1:A8<>"")/COUNTIF(A1:A8,A1:A8&""))

If no blank cells, you could just try:
=SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8))
 

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

SUMPRODUCT to calculate unique occurences of string in column of d 3
Counting unique values in column 3
Counting number of unique datafields in a column 1
Conditional Sumproduct 0
Filtering of data in a master excel sheet from multiple sheets 3
counting unique values of text 4
Counting unique combinations in two columns 3
How to update data from multiple sheets to one specific sheets 4

Top