[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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.
0
dbbishop
Asked:
dbbishop
  • 4
  • 3
  • 2
1 Solution
 
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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now