I found this fun challenge on the Mr. Excel forums.

You can count unique items in a list with an array formula described on Chip’s site. I use a variant of it below.

The problem is that if you have a list of dates and times and you only want a count of the unique days. To disregard the times, try the below formula.

Interesting how different people approach a problem. Array formulas are awesome, as long as you don’t plan on sharing the workbook… 99.9% of Excel users are totally mystified by them. I wrote a function that counts the unique values in an array. It was easy enough to create a function that converts the range to an array of dates then pass it to this function. Here it is!

Option Explicit

Option Base 1

Function CountDays(rng As Range)

‘Counts the number of unique days in a range

Dim i As Long, n As Long, u As Long

Dim dVals() As Long

ReDim dVals(65536)

n = Int(rng.count)

i = 1

‘Search in each (valid) cell in the range

u = 0

For i = 1 To n

‘If the cell is a date, add it to a new array

If IsDate(rng(i)) Then

u = u + 1

dVals(u) = Int(rng(i))

End If

Next i

If u > 0 Then

‘Pass the array of dates to a function that counts

‘the number of unique values in an array

ReDim Preserve dVals(u)

CountDays = CountUnique(dVals)

Else

‘Return an error value if there are *no* dates

CountDays = “#N/A “

End If

End Function

Function CountUnique(Arr As Variant) As Long

‘Counts the number of unique values in an array

‘Assumes Option Base 1

Dim i As Long, n As Long, k As Long, u As Long

Dim UniqueVals() As Variant

Dim IsUnique As Boolean

n = UBound(Arr)

ReDim UniqueVals(n)

u = 1

UniqueVals(1) = Arr(1)

For i = 1 To n

k = 1

IsUnique = False

Do While k < = u

If Arr(i) <> UniqueVals(k) Then

IsUnique = True

k = k + 1

Else

IsUnique = False

Exit Do

End If

Loop

If IsUnique Then

u = u + 1

UniqueVals(u) = Arr(i)

End If

Next i

CountUnique = u

End Function

How about this one

Function CountUniqueDays(rRng As Range) As Long

Dim cDays As Collection

Dim rCell As Range

Set cDays = New Collection

On Error Resume Next

For Each rCell In rRng.Cells

If IsDate(rCell.Value) Then

cDays.Add rCell.Value, CStr(CLng(rCell.Value))

End If

Next rCell

On Error GoTo 0

CountUniqueDays = cDays.Count

End Function

Dick, that’s nice! You did it in about 1/4 of the lines of code. I’d never even heard of a “Collection” before. I have a feeling knowing about this would have saved me a lot of time coding. For instance, I never would have had to write that CountUnique function…

The collection object might be a good topic for a future post!

“The collection object might be a good topic for a future post!”

Geez, why didn’t I think of that. Thanks, Matt.

Do you have any suggestions as to expanding this functions with a condition?

I am trying to count unique values/dates per period, where I have the dates in one column and the periods(numeric representation of the month) in another column (if I can discard this latter column that would be even better).

Thanks in advance,

Koos

why not use an advanced filter, with unique=true?

then count that?

i want to know about the basics of macro programming in excel.

hi this is not a comment it looks more as a question:-)

SUMPRODUCT((WEEKDAY(ROW(INDIRECT($A$3&”:”&$B$2))=D7))*((DAY(ROW(INDIRECT($A$3&”:”&$B$2)))=13))

for Excel guru’s the know already for what I’m looking for;right the number of weekdays(D7)&the 13th

my result =0

plrese help me

Matt,

Converting to a Long with CLng rounds decimals. Since the time portion of the date value is a decimal, you can easily end up with different dates being counted as the same (4pm of one day is the same as 4am the next day after rounding).

To fix it, use Int to truncate the decimal instead:

If IsDate(rCell.Value) Then

cDays.Add rCell.Value, CStr(Int(rCell.Value))

End If

I used Dick’s original formula that started this thread and received the following error:

A value used in the formula is of the wrong data type set.

When I show the calculate steps used in evaluating the formula, it indicates that the range of data I selected will result in an error.

I formatted the entire range of cells as ‘Date’ > ‘3/14/01 13:30’ to match the example, but the error is still returned.

I simply replaced the example range with my range.

Here is the exact formula text:

=SUM(IF(LEN(I7:I46),1/(COUNTIF(I7:I46,”<“&INT(I7:I46)+1)-COUNTIF(I7:I46,”<“&INT(I7:I46)))))

Am I missing a step? Thanks.

Matthew: Make sure you’re entering with Control+Shift+Enter as it is an array formula. If you are, then format all of your dates as ‘General’. This will make them look like numbers and any text you have in the list will stand out (because it will still look like a date).

The above function not working…

=SUM(IF(LEN(I7:I46),1/(COUNTIF(I7:I46,”<“&INT(I7:I46)+1)-COUNTIF(I7:I46,”<“&INT(I7:I46)))))

Hi, I was trying to use this formula to count unique days but I was not able. Could you please help me?

I have a column F6 to F36 with short date and I have the following dates long the column: 3/10, 3/11, 3/12, 3/13. I would like to count how many unique days I have. I tried the above formula but I got the “#VALUE!”.. Any idea?

Thanks in advance!!