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!

Answer Wiki

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SbElectric
    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
    2,540 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following