Conditional formatting in Microsoft Excel 2003
Home > IT Answers > Microsoft Windows > Conditional formatting in Microsoft Excel 200...
Bmutton
5 pts.
0
Q:
Conditional formatting in Microsoft Excel 2003
Microsoft Excel 2003, Conditional formatting, Microsoft Excel 2003 formulas, Microsoft Excel
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!
ASKED: May 13 2009  6:59 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Kccrosser
1850 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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:

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


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")
Last Answered: May 13 2009  4:00 PM GMT by Kccrosser   1850 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

SbElectric   1625 pts.  |   May 13 2009  10:01PM GMT

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

 
0