Count the number of unique dates

[drewmedic23]

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!!

 

[Deepak]

Using a UDF will solve the issue.

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.

 

[Colin Legg]

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.

 

[Look]

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.

 

[Dilipxl]

={SUM(1/COUNTIFS(Range,Range))}... also will do the trick to know unique items (Date,Name ..etc)

 

[Debraj]

={SUM(1/COUNTIFS(Range,Range))}

Hi Dilip!

Welcome to the forum... thanks for the solution..

Just an addition.. Range, should cont contain any blank cell.. In case of Blank cell exist.. follow @Colin Legg 's suggestion..

 

[Somendra Misra]

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]

Here is one more...

=SUM(SIGN(FREQUENCY(A1:A4,A1:A4)))

 

[Herbert Seidenberg]

And yet another...
DISTINCTCOUNT()
A DAX formula in PowerPivot.

 

[Colin Legg]

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

 

[shrivallabha]

I think I had seen it in the Excel's native help but not sure(either in 2007 or 2003 but can't find it in 2010). I will try to search it in the office where I still have 2007.

MS have also tried to address it.
http://office.microsoft.com/en-in/e....aspx#BMcount_the_number_of_unique_values_by_

 

[drewmedic23]

Thank you all! I ended up using Haseeb A's formula.