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

I need a SUMIF that works with two criteria
 

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.


"alondon" wrote:

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

I need a SUMIF that works with two criteria
 

Just for information:

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

--
Regards,
Tom Ogilvy

"Martin Fishlock" wrote in message
...
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.


"alondon" wrote:

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