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