I need a SUMIF that works with two criteria
Folks,
Is there some way to build with some combination of existing Excel 2003
functions the equivilent of a SUMIF command for two critreria. My criteria
are Division and Statement Classification (e.g. Sum all Division 10 Sales
acounts). I get a feeling that some combination of Sumif used with an array
formula would due the trick - but, I can't seem to find the right
combination.
I built a UDF to do this, but with about 6000 cells (12 months 500
accounts), it is just too slow. It takes 8 to 10 seconds for each division
and I have 13 divisions. I am running Excel 2003 with a gig of memory and
an old 1.5 GHz single core CPU. Below is the UDF Code, perhaps there is a
way to speed this up?
Thanks for your help.
Allan P. London, CPA
__________________________________________________ ____________________
Function RegionAmtMo(Dept As Integer, FSLabel As String, Mode As String) As
Double
Dim theRow As Variant
Dim theValue As Double
Dim theColumn As Integer
theValue = 0
If Mode = "Actual" Then
theColumn = 8
Else
theColumn = 10 'Budget
End If
For Each theRow In Range("AcctList").Rows
If theRow.Cells(1, 1).Value = Dept And theRow.Cells(1, 5) = FSLabel
Then
theValue = theValue + theRow.Cells(1, theColumn).Value
End If
Next theRow
RegionAmtMo = theValue
End Function
|