I have a custom 'User Defined Function' (UDF) which can do this.
There may be a way to do this with standard formulas, but if not you are welcome to use this UDF.
UDF's are like standard functions in Excel and are entered with an = sign.
The difference is that they are written to do specific tasks. Also UDF's are typically written in Visual Basic for Applications (VBA) and run more slowly than equivalent built-in functions - not usually noticeable, and of course you have to get the UDF loaded and running on your PC.
In Excel 2007 I would save the function in a macro-enabled Add-In format (*.xlam)
Start a blank workbook.
From the Office button - Save As - you will need to select 'Other Formats', then find *xlam
Name the file something like CustomCount.xlam, and keep the default location (...\AddIns).
Excel will revert to a new Book1 or similar.
Now set the xlam file to load every time Excel starts
From the Office button select Excel Options (at the bottom of the dialog box)
From the left side select the Add-Ins Tab
At the bottom select Excel Add-Ins in the 'Manage:' drop down and click 'Go'
In the Add-Ins dialog box click Browse
You should see your new xlam file. Select it and click OK
The file will show in the Add-Ins dialog box and should already be checked.
The xlam Add-In will now load every time Excel starts.
Now use Alt + f11 (the Alt key and function key #11 together).
This opens the VBA window.
In the Visual Basic window that opens, look for 'VBAProject(CustomCount.xlam)' in the Project Explorer pane.
If the Project Explorer pane is not visible, from the VB menu select 'View' and 'Project Explorer'
Right-click on VBAProject(CustomCount.xlam) and select 'Insert' and 'Module' (not Class module)
Find the new module (typically Module1) in the 'Modules' part of the tree, below VBAProject(CustomCount.xlam)
Double click the new module's name.
In the large VB window on the right, enter this:
Public Function CountUniqueV( _
txt As String, rng1 As Range, rng2 As Range)
'counts number of occurrences of txt in rng1,
'but only once per value in rng2
Dim rngCell As Range
Dim strComboText As String
Dim strComboArry() As String
Dim intUsed As Integer
Dim blnSaved As Boolean
Dim n As Integer
'set used array elements counter
intUsed = 0
'resize array to max possible
For Each rngCell In rng1
If rngCell.Text = txt Then
'flag that we haven't saved this
blnSaved = False
'get combination text
strComboText = rngCell.Text & _
For n = 0 To UBound(strComboArry, 1)
If strComboArry(n) <> strComboText Then
If strComboArry(n) = "" Then
'no match and we've searched to end of data
'so save the value
strComboArry(n) = strComboText
'flag that its been saved
blnSaved = True
'saved already so flag it
blnSaved = True
If blnSaved = True Then Exit For
'now count number of unique instances
For n = 0 To UBound(strComboArry)
If strComboArry(n) <> "" Then
intUsed = intUsed + 1
'return the count
CountUniqueV = intUsed
From the VB menu click 'File' then 'Save CustomCount.xlam
Then 'Close and Return to Microsoft Excel'
In your standard excel workbook select the worksheet with the Initials/dates in a column.
In an empty cell enter a formula like this:
text to find can be a text string or a reference to a cell containing the text.
The search range contains the text to be searched. In your case the text containing the initials.
duplicates range is a range of the same size and same rows, but containing the duplicate data. In your case dates.
Note that this function uses the text in the duplicate range, so if you use dates, they must all look the same, either present as text or if dates, then all formatted the same.
06/06/2010 will be considered as different to July 06, 2010
This is what the function looked like when I tested it.
Overall - if someone comes up with a formula based solution using built-in functions, then go with that. If not, then give this a go.