## 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”)

## Discuss This Question: 1 Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
• Unless I am missing something – this is a classic case of using Pivot Table. I used your data in Excel 2007 & got this type of result. Row Labels A B Grand Total 2007 2 3 5 2008 1 2 3 2009 3 1 4 Grand Total 6 6 12 Is this what you have in mind? You may just read up on Pivot Table. By the way, Pivot table handles large files. I noticed that the columns did not line up properly when I copied & pasted Excel data. There are 4 columns - Year, A, B, and Grand Total