Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Holiday spreadsheet - SUMIF forumla no longer works? | Excel Worksheet Functions | |||
SUMIF formula works in Excel 2003, does not work in Excel 2007 | Excel Discussion (Misc queries) | |||
SUMIF FUNCTION works in all cells but those related to one account | Excel Worksheet Functions | |||
Criteria error but still works | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions |