Solved

Count of Distinct Values

Posted on 2013-12-10
9
265 Views
Last Modified: 2013-12-11
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
Comment
Question by:dbbishop
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Author Comment

by:dbbishop
ID: 39710130
p.s. the data is not sorted.
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39710137
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
 
LVL 6

Accepted Solution

by:
JJSmith earned 500 total points
ID: 39710152
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
 
LVL 9

Expert Comment

by:guswebb
ID: 39710156
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Expert Comment

by:JJSmith
ID: 39710179
My bad - but it's the formula rather than the data range that was requested.
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39710184
Indeed and it was already provided!
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 39710402
Works like a champ.
0
 
LVL 9

Expert Comment

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

Author Comment

by:dbbishop
ID: 39711309
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now