## Conditional formatting in Microsoft Excel 2003

5 pts.
Tags:
Conditional formatting
Microsoft Excel
Microsoft Excel 2003
Microsoft Excel formulas
Basically I have 2 columns the first column has years in it & the second has random letters. Now, going down the 1st column it reads: 2007,2007,2007,2007,2007,2008,2008,2008,2009,2009,2009,2009 And going down the Second Column it reads: A,B,A,B,B,B,A,B,A,A,A,B Note: This is just a segment of the data table. What i need is formula that will answer the following question, when a applied to the ranges in column 1 & 2. Question: In 2009 'A' Occurred...(FORMULA IN HERE) Times. Question: In 2009 'B' Occurred... (Formula Here) Times. I've tried everything - COUNTIF & SUMPRODUCT. I also have Excell 2003. Please help me this is driving me insane!

Thanks. We'll let you know when a new response is added.

There is no built-in function that I know of that will solve that, but you can write a simple user function.

Go to Tools -> Macro -> VisualBasic Editor.
Go to Insert -> Module, which will create a new code module
Paste in the following code:

<pre>Function CountPair(R As Range, pYear As Integer, pChar As String) As Integer
Dim i As Integer
Dim iTotal As Integer

iTotal = 0
For i = 1 To R.Rows.Count
If R.Cells(i, 1) = pYear And R.Cells(i, 2) = pChar Then
iTotal = iTotal + 1
End If
Next ‘ i

CountPair = iTotal
Exit Function

End Function</pre>

Then, in the original spreadsheet, define a range that covers all the cells of interest and define a name for the rante. In your formula for the cell for 2007/”A”, put:

=CountPair(RangeName, 2007, “A”)