Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
Since understanding and using the sumproduct() function I use it extensively
in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
You may want to look at Pivottables. They may be able to the same summaries
based on your multi-criteria rules. Jim May wrote: Since understanding and using the sumproduct() function I use it extensively in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
"Jim May" wrote in message
... Since understanding and using the sumproduct() function I use it extensively in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May Obviously 1000 similar formulas will take about 1000 times as long to calculate as one formula, so making each as quick as possible is fruitful if you need a lot of formulas. I have found two approaches that significantly reduce computation time, which may or may not be applicable in your situation. The first is to keep the ranges as short as possible. In my situation, I had data covering a one year period, with dates in column A in ascending order, and I was summarising by month. My original formulas had ranges covering the whole year. I added a few new formulas on a new sheet to find the first and last lines for each month, and used the results of these to define named ranges for each month. My SUMPRODUCT formulas then had ranges on average only 1/12 as long. The second is to look at the formulas that are used many times and see if there is any part of the calculation that literally is repeated in each. For example, originally in one of my SUMPRODUCT conditions I had something like (A1:A10000=MAX(Sheet2!$Z$1:$Z$5)). Copying this formula 1000 times meant that Excel was evaluating MAX(Sheet2!$Z$1:$Z$5) 1000 times. By doing this bit in another cell and using its result in the SUMPRODUCT condition, it is only evaluated once. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
One approach I have used to speed things up is to convert SUMPRODUCT
and SUM(IF(... array formulae to SUMIF, which is very much quicker. Essentially you need to use other helper columns to join the multi- criteria columns together into one (and preferably fix the values), which can then be used with SUMIF. Hope this helps. Pete On Nov 21, 2:47 pm, Jim May wrote: Since understanding and using the sumproduct() function I use it extensively in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
=SUMPRODUCT((VisionPaymtsPosted!$A$5:$A$12000=Carr yOverRecon!$C8)*(VisionPaymtsPosted!$E$4:$AC$4=Car ryOverRecon!W$5)*(VisionPaymtsPosted!$E$5:$AC$1200 0))
Here is an example from one sheet where there this formula is in 26 other columns and 80 rows. How could I incorporate this into the Sumif() function? "Pete_UK" wrote: One approach I have used to speed things up is to convert SUMPRODUCT and SUM(IF(... array formulae to SUMIF, which is very much quicker. Essentially you need to use other helper columns to join the multi- criteria columns together into one (and preferably fix the values), which can then be used with SUMIF. Hope this helps. Pete On Nov 21, 2:47 pm, Jim May wrote: Since understanding and using the sumproduct() function I use it extensively in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
Jim,
I was describing a situation where you might have several 1-D arrays of the same size, eg: =SUMPRODUCT((A1:A40000="Pete")*(MONTH(B1:B40000)=1 1)*(YEAR(B1:B40000)=2007)*(C1:C40000="Y")*(D1:D400 00)) Columns A, B and C can be joined in a way to match the criteria required, say in column E, and then a SUMIF can replace the formula above. I'm not sure how you would apply this to your case, where you seem to have a 2-D table. Pete On Nov 21, 6:57 pm, Jim May wrote: =SUMPRODUCT((VisionPaymtsPosted!$A$5:$A$12000=Carr yOverRecon!$C8)*(VisionPa-ymtsPosted!$E$4:$AC$4=CarryOverRecon!W$5)*(VisionP aymtsPosted!$E$5:$AC$1200-0)) Here is an example from one sheet where there this formula is in 26 other columns and 80 rows. How could I incorporate this into the Sumif() function? "Pete_UK" wrote: One approach I have used to speed things up is to convert SUMPRODUCT and SUM(IF(... array formulae to SUMIF, which is very much quicker. Essentially you need to use other helper columns to join the multi- criteria columns together into one (and preferably fix the values), which can then be used with SUMIF. Hope this helps. Pete On Nov 21, 2:47 pm, Jim May wrote: Since understanding and using the sumproduct() function I use it extensively in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
Thanks Pete, In your example what would be the content of Column E?
and also Col F with the Sumif(), maybe =Sumif(E1:E40000,???,D1:D40000) "Pete_UK" wrote: Jim, I was describing a situation where you might have several 1-D arrays of the same size, eg: =SUMPRODUCT((A1:A40000="Pete")*(MONTH(B1:B40000)=1 1)*(YEAR(B1:B40000)=2007)*(C1:C40000="Y")*(D1:D400 00)) Columns A, B and C can be joined in a way to match the criteria required, say in column E, and then a SUMIF can replace the formula above. I'm not sure how you would apply this to your case, where you seem to have a 2-D table. Pete On Nov 21, 6:57 pm, Jim May wrote: =SUMPRODUCT((VisionPaymtsPosted!$A$5:$A$12000=Carr yOverRecon!$C8)*(VisionPa-ymtsPosted!$E$4:$AC$4=CarryOverRecon!W$5)*(VisionP aymtsPosted!$E$5:$AC$1200-0)) Here is an example from one sheet where there this formula is in 26 other columns and 80 rows. How could I incorporate this into the Sumif() function? "Pete_UK" wrote: One approach I have used to speed things up is to convert SUMPRODUCT and SUM(IF(... array formulae to SUMIF, which is very much quicker. Essentially you need to use other helper columns to join the multi- criteria columns together into one (and preferably fix the values), which can then be used with SUMIF. Hope this helps. Pete On Nov 21, 2:47 pm, Jim May wrote: Since understanding and using the sumproduct() function I use it extensively in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct() - Next best alternative
E1 could have a formula like this:
=A1&TEXT(B1,"mm")&TEXT(B1,"yy")&C1 and copied down (preferably with the values fixed as well if the data is not likely to change). Then if you have G1 for a name, H1 for a date and I1 for a letter, you could use this in J1: =SUMIF(E1:E40000,G1&TEXT(H1,"mm")&TEXT(H1,"yy")&I1 ,D1:D40000) The same function used to set up the combination in column E is used in the middle parameter of the SUMIF - often it would just be a straight concatenation, but here I wanted to pick out monthly data from a particular year. Hope this helps. On Nov 22, 12:52 pm, Jim May wrote: Thanks Pete, In your example what would be the content of Column E? and also Col F with the Sumif(), maybe =Sumif(E1:E40000,???,D1:D40000) "Pete_UK" wrote: Jim, I was describing a situation where you might have several 1-D arrays of the same size, eg: =SUMPRODUCT((A1:A40000="Pete")*(MONTH(B1:B40000)=1 1)*(YEAR(B1:B40000)=2007)-*(C1:C40000="Y")*(D1:D40000)) Columns A, B and C can be joined in a way to match the criteria required, say in column E, and then a SUMIF can replace the formula above. I'm not sure how you would apply this to your case, where you seem to have a 2-D table. Pete On Nov 21, 6:57 pm, Jim May wrote: =SUMPRODUCT((VisionPaymtsPosted!$A$5:$A$12000=Carr yOverRecon!$C8)*(VisionPa--ymtsPosted!$E$4:$AC$4=CarryOverRecon!W$5)*(VisionP aymtsPosted!$E$5:$AC$120-0-0)) Here is an example from one sheet where there this formula is in 26 other columns and 80 rows. How could I incorporate this into the Sumif() function? "Pete_UK" wrote: One approach I have used to speed things up is to convert SUMPRODUCT and SUM(IF(... array formulae to SUMIF, which is very much quicker. Essentially you need to use other helper columns to join the multi- criteria columns together into one (and preferably fix the values), which can then be used with SUMIF. Hope this helps. Pete On Nov 21, 2:47 pm, Jim May wrote: Since understanding and using the sumproduct() function I use it extensively in the design of some rather sophisticated spreadsheets. With several sheets (in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data - with amaybe 3 or 5 columns of the sumproduct() function in each row. It is the availability of "multi-criteria" (which I need) that draws me to using the Sumproduct. The recalculation (using the tools options, calculation, automatic) is terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes of "calculating" before the system is available to continue. Am I misusing the function or is there an alternative that I could use and get the pay-back of less calc time? Tks in advance, Jim May- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF fn Alternative | Excel Discussion (Misc queries) | |||
ALTERNATIVE TO SUMPRODUCT NEEDED | Excel Worksheet Functions | |||
If alternative | Excel Worksheet Functions | |||
Alternative to SUMPRODUCT? | Excel Discussion (Misc queries) | |||
Which is faster? SUMPRODUCT or VLOOKUP, or another alternative? | Excel Worksheet Functions |