# 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
Asked:
###### Who is Participating?

Commented:
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

Author Commented:
p.s. the data is not sorted.
0

Commented:
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

Commented:
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

Commented:
My bad - but it's the formula rather than the data range that was requested.
0

Commented:
Indeed and it was already provided!
0

Author Commented:
Works like a champ.
0

Commented:
It would be useful to know what it was that you didn't like about the solution which was offered first (my one)?
0

Author 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.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.