Help with count if function

Microsoft Microsoft excel 2007 full vers...
June 30, 2010 at 08:18:06
Specs: Windows XP
We have several instuctors that teach several different classes. I want to count the number of times initials appear in an excel column without repeating a date in another column.

Here is a small example of the set of data.

5/19/2010 RW
5/19/2010 RW
5/19/2010 RW
5/19/2010 RW
5/19/2010 RW
6/2/2010 TF

6/2/2010 TF
6/2/2010 TF
6/2/2010 TF
6/2/2010 TF
6/2/2010 TF

6/2/2010 TF

6/16/2010 RW
6/16/2010 RW
6/16/2010 RW
6/16/2010 RW

6/16/2010 RW
6/16/2010 RW
6/16/2010 RW

See More: Help with count if function

Report •

June 30, 2010 at 09:07:47

If the data is in cells A2 to A25
and the letters are always the last two characters in the data, then use this formula:
"RW" can be replaced with a reference to another cell containing "RW"
Then you could have a list of the initials in one column and the formula in an adjacent column:

	C	D
2	RW	12
3	TF	7

The formula in D2 is:
Then drag it down to extend it alongside the column of initials.

If the initials are not always the right two characters, or if you need to use some 3-character initials, this slightly longer version will work:

In all of these, change $A$2:$A$25 to match the range of data, but keep the $ signs, so that the formula can be extended by dragging, and still refer to the correct range.


Report •

June 30, 2010 at 09:20:47
Thank you for your response. I may not have been clear. The initials are in one column and the date is in an adjacent.
The expected output for the example above should be something like:
TF I 1
RW I 2

Each line represents a student that took the class and the initials represent the instuctor. I want to show how many classes, not students, each instuctor taught. I hope this was clear. Thank you for any help

Report •

June 30, 2010 at 09:28:55

Does that mean that you want to count the Instructor (by initials) but only once per date, so 5/19/2010 RW appearing 5 times is only counted once.


Report •

Related Solutions

June 30, 2010 at 11:17:33
yes exactly! but this is only one class and I want to total each class at a time for each instructor.

Report •

June 30, 2010 at 12:07:49

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.
Click OK
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:

Option Explicit

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
ReDim strComboArry(rng1.Rows.Count)

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 & _
                    ActiveSheet.Cells(rngCell.Row, rng2.Column).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
                End If
                'saved already so flag it
                blnSaved = True
            End If
            If blnSaved = True Then Exit For
        Next n
    End If
Next rngCell

'now count number of unique instances
For n = 0 To UBound(strComboArry)
    If strComboArry(n) <> "" Then
        intUsed = intUsed + 1
    End If
Next n

'return the count
CountUniqueV = intUsed
End Function

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.


Report •

Ask Question