Conditional formatting in Microsoft Excel 2003
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!
Software/Hardware used:
Software/Hardware used:
ASKED:
May 13, 2009 6:59 AM
UPDATED:
May 13, 2009 10:01 PM
Answer Wiki:
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")
To see all answers submitted to the Answer Wiki: View Answer History.
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