• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count the number of unique dates

drewmedic23

New Member
Hello all,
I am trying to figure out a function to give me the sum of all unique values (dates) in my table.
In column A, I have the dates. Some days have multiple entries. I am trying to get a function which will tell me the number of unique dates. So if the entries in A1:A4 contain 4/1/14, 4/2/14, 4/2/14, 4/3/14, respectivelly, I would want the formula to output "3". I'm fairly new to excel and have looked around but really haven't fount anything.
Thanks!!
 
Using a UDF will solve the issue.
Code:
Public Function COUNTU(theRange As Range) As Variant
    Dim colUniques As New Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Range
    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    If Not (IsArray(vArr)) Then vArr = Array(vArr)
    On Error Resume Next
    For Each vCell In vArr
    If vCell <> vLcell Then
        If Len(CStr(vCell)) > 0 Then
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell
   
    COUNTU = colUniques.Count
End Function

Use as =COUNTU(B15:B18) & change the range as required.
 
In terms of native functions you can use a formula such as this:

=SUMPRODUCT(--(FREQUENCY(A1:A4,A1:A4)>0))

For more information on this formula see here.

But I agree with Deepak that a custom UDF could be more performant. I had a go at writing a COUNTDISTINCT() function which uses a Dictionary object here if you're interested.
 
You can do this with INDEX(MATCH(COUNTIF formulas combined, try out the example and see if it works for you.

Just change $A$12 in the formulas in column B to the reference of the last date in column A in your spreadsheet and it should work.
 

Attachments

  • UniqueDates.xlsx
    9.4 KB · Views: 67
Hi Dilip,

Just to addition of @Debraj suggestion regarding blanks in range you can modify your formula to:

=SUMPRODUCT(--(Range<>""),1/COUNTIFS(Range,Range&""))

This will remove the necessity of Ctrl+Shift+Enter. But just to make a note this will be much slower than @Colin Legg suggestion of using Frequency function to count unique.

Regards,
 
@Haseeb A

Hey, that's cool. I'd missed that the formula can be done with just SUM() rather than SUMPRODUCT().
[My formula variation: =SUM(--(FREQUENCY(A1:A4,A1:A4)>0)) ]


Now I'll have to go and update that blog post... :rolleyes:
 
Last edited:
  • Like
Reactions: Aby
Back
Top