Count distinct function?...

[lenny3k]

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

 

[brooklyn2007]

Nope, it won't work. It shows 0 instead of 6 which is the right answer. Please check the upload file.

http://www.mediafire.com/?ka5tzl9z0vilcu9

Never mind, it worked finally. It was the other way around, g2 - t104 and h2 - october. Thanks

 

[wcufflin]

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

 

[Aladin Akyurek]

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:

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

 

[Skeete]

Or you could write your own custom function

An inefficient method :
___________________________________________________

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