Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I have a workbook with 2,600 SUMPRODUCT formulas, each looking for 5
conditions as follows: =SUMPRODUCT((PLANS!$A$5:$A$25000=TEXT($A9,"#"))*(P LANS!$B$5:$B$25000=$A$4)*(PLANS!$E$5:$E$25000=$D$3 )*(PLANS!$F$5:$F$25000=D$4)*(PLANS!$K$5:$K$25000=$ E$3),PLANS!$H$5:$H$25000) The sheets "PLANS" is an external database that refreshes from the company server. The problem is that opening the file takes a long time. How can I speed up the process? Tony. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
to speed up the opening the file, if data is not being refreshed too
frequently, you might want to set calculation to manual. Then press F9 when you want to take the time to recalculate everything. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tony7659" wrote: I have a workbook with 2,600 SUMPRODUCT formulas, each looking for 5 conditions as follows: =SUMPRODUCT((PLANS!$A$5:$A$25000=TEXT($A9,"#"))*(P LANS!$B$5:$B$25000=$A$4)*(PLANS!$E$5:$E$25000=$D$3 )*(PLANS!$F$5:$F$25000=D$4)*(PLANS!$K$5:$K$25000=$ E$3),PLANS!$H$5:$H$25000) The sheets "PLANS" is an external database that refreshes from the company server. The problem is that opening the file takes a long time. How can I speed up the process? Tony. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I 'm not sure whith the amount of data you have if any method is going to be
quicker. If you set the workbook for manual calucaltion the workbook will open quickly but the manual calculation will take a long time. If you open the workbook often but only need to do the update infrequently this is a good option. Sumproduct is know for being slow. Writing a macro may speed things up but I don't know for sure. I fyou could sort the data then you don't have to use Sumproduct and a macro will definitely be quicker. I would sort the 5 criteria using two sorts since excel doesn't support more than 3 items in a sort a one time. First do 2 columns sort and then repeat the sort for the other 3 columns. Now all your data wil be together. I can then write a simple macro which will add up the different groups that wil run much quicker that sumproduct. I can even create a new worksheet where the summary results will be placed. "Tony7659" wrote: I have a workbook with 2,600 SUMPRODUCT formulas, each looking for 5 conditions as follows: =SUMPRODUCT((PLANS!$A$5:$A$25000=TEXT($A9,"#"))*(P LANS!$B$5:$B$25000=$A$4)*(PLANS!$E$5:$E$25000=$D$3 )*(PLANS!$F$5:$F$25000=D$4)*(PLANS!$K$5:$K$25000=$ E$3),PLANS!$H$5:$H$25000) The sheets "PLANS" is an external database that refreshes from the company server. The problem is that opening the file takes a long time. How can I speed up the process? Tony. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hello,
I suggest to have a look at my UDF Pstat: http://sulprobil.com/html/listfreq.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct help | Excel Worksheet Functions | |||
sumproduct | Excel Worksheet Functions |