Microsoft Microsoft excel 2007 full vers...

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

Hi, 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:

=SUMPRODUCT((RIGHT($A$2:$A$25,2)="RW")*(1))

"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:

=SUMPRODUCT((RIGHT($A$2:$A$25,2)=C2)*(1))

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:

=SUMPRODUCT((NOT(ISERROR(FIND(C2,$A$2:$A$25))))*(1))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.

Regards

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 2Each 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

Hi, 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.

Regards

yes exactly! but this is only one class and I want to total each class at a time for each instructor.

Hi, 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 Else '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:

=countUniqueV(text_to_find,search_range,duplicates_range)

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 thetextin 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, 2010This is what the function looked like when I tested it.

=countUniqueV(E2,$B$2:$B$25,$A$2:$A$25)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.

Regards

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History