I need a SUMIF that works with two criteria

[alondon]

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

 

[Guest]

Allan,

In the example below the data keys are in a1:a6 and b1:b6 and the data is in
c1:c6

and the match keys are in a9 and b9.

[C9]=SUMPRODUCT(($A$1:$A$6=A9)*($B$1:$B$6=B9),$C$1:$C$6)

Note this is an array formula so you have to press Ctrl+Shift+Enter when you
enter the formula in C9, but you can can if down ok.

The other way to do it is to have an extra column with a combination key
(a1&":"&b1) and do a sumif on the new column.

 

[Tom Ogilvy]

Just for information:

Sumproduct doesn't need to be entered with Ctrl+Shift+Enter in most cases -
such as this one.

--
Regards,
Tom Ogilvy

Allan,

In the example below the data keys are in a1:a6 and b1:b6 and the data is
in
c1:c6

and the match keys are in a9 and b9.

[C9]=SUMPRODUCT(($A$1:$A$6=A9)*($B$1:$B$6=B9),$C$1:$C$6)

Note this is an array formula so you have to press Ctrl+Shift+Enter when
you
enter the formula in C9, but you can can if down ok.

The other way to do it is to have an extra column with a combination key
(a1&":"&b1) and do a sumif on the new column.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.

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