Count distinct function?...

lenny3k

New Member
Joined
Jan 15, 2003
Messages
18
Hi there.

Is there a way to count distinct values in a range of cells in a worksheet?

i'm looking for something like a COUNT DISTINCT function that would count all distinct values in an entire column. any ideas?

any help would be appreciated!

Len
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Du you mean count unique?
(English is not my native tongue)


Then try:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

fairwinds this is brilliant!!! I learned some new things. Thanks for sharing.

In my situation, I had a list of shipments, with delivery dates and delivery addresses. I wanted to find the number of unique delivery addresses for each delivery date.

I slightly modified the criteria in your formula and got the results I needed:
=SUMPRODUCT((E15:E21=G2)/COUNTIF(F15:F21,F15:F21&""))

Where:
- Column E contains the delivery dates
- G2 contains a specific delivery date
- Column F contains delivery addresses
 
Upvote 0
fairwinds this is brilliant!!! I learned some new things. Thanks for sharing.

In my situation, I had a list of shipments, with delivery dates and delivery addresses. I wanted to find the number of unique delivery addresses for each delivery date.

I slightly modified the criteria in your formula and got the results I needed:
=SUMPRODUCT((E15:E21=G2)/COUNTIF(F15:F21,F15:F21&""))

Where:
- Column E contains the delivery dates
- G2 contains a specific delivery date
- Column F contains delivery addresses

Control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF(F15:F21<>"",IF(E15:E21=G2,
  MATCH(F15:F21,F15:F21,0))),ROW(F15:F21)-ROW(F15)+1),1))

is a tad faster and robust against empty/blank cells.
 
Upvote 0
Or you could write your own custom function

An inefficient method :
___________________________________________________

Code:
Public Function COUNTDISTINCT(inputR As Range) As Integer


Dim countR As Variant
Dim Inpr As Variant
Inpr = inputR


ReDim countR(1 To 1)
countR(1) = Inpr(1, 1)
For N = 1 To UBound(Inpr)
   counter = 0
   For x = 1 To UBound(countR)
      If Inpr(N, 1) = countR(x) Then counter = counter + 1
   Next x
   If counter = 0 Then
      ReDim Preserve countR(1 To UBound(countR) + 1)
      countR(UBound(countR)) = Inpr(N, 1)
   End If
Next N


CountDistinct = UBound(countR)
End Function
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top