Count of Distinct Values

I have a column that might contain the following rows:
1
1
2
4
3
2
1
4
3
2
1
2
3
As you can see, in this case, there are 4 distinct values. I want a summary cell that displays the number of distinct values (4). I prefer (require) this to be a formula vs. VBA code.
LVL 15
dbbishopAsked:
Who is Participating?
 
JJSmithCommented:
Assuming the column is A, then:

=SUM(IF(FREQUENCY(A1:A12,A1:A12)>0,1))

should do the trick - it's a little bit of a cheat as the data and bin ranges are the same - but has the desired outcome.
0
 
dbbishopAuthor Commented:
p.s. the data is not sorted.
0
 
guswebbCommented:
If your values are in column A from rows 1 through to 13 then use this formula.

=SUMPRODUCT((A1:A13<>"")/COUNTIF(A1:A13,A1:A13&""))
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
guswebbCommented:
There are 13 rows in his data set so you would be missing the final value, not that it affects the count in this example.
0
 
JJSmithCommented:
My bad - but it's the formula rather than the data range that was requested.
0
 
guswebbCommented:
Indeed and it was already provided!
0
 
dbbishopAuthor Commented:
Works like a champ.
0
 
guswebbCommented:
It would be useful to know what it was that you didn't like about the solution which was offered first (my one)?
0
 
dbbishopAuthor Commented:
I tried both. Yours worked for a short list, but when I tried it with 40,000 rows, which was the real data, the solution posted by JJSmith was CONSIDERABLY faster. Furthermore, with the increased row count, yours did not return the correct value (consistently returned 0). I tried yours with 10,000 rows and it returned the correct value, but 20,000 rows returned 0. Don't know where the cutoff was.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.