SUMPRODUCT

[Tony7659]

I have a workbook with 2,600 SUMPRODUCT formulas, each looking for 5
conditions as follows:

=SUMPRODUCT((PLANS!$A$5:$A$25000=TEXT($A9,"#"))*(PLANS!$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.

 

[Luke M]

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.

 

[Joel]

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.

 

[Bernd P]

Hello,

I suggest to have a look at my UDF Pstat:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd