Sumproduct tuning

[David]

Greetings and TIA for your help
I have a monster spreadsheet with most of the action on 3 worksheets. There
are 18k sumproduct formulas in the workbook, each trawling through 9k records
on a worksheet in the same book. The final outputs are fine but, of course,
the problem is time to calculate. I'm running on a P4 1.7GHz, 1GB Ram, XP
pro, Excel 2002 system. To recalculate the workbook takes in excess of 40
minutes. I have a short macro on a button on each sheet to recalculate the
selection only, and I use this or recalcullate the active sheet only when
appropriate. Any ideas how I might speed things up?

 

[Frank Kabel]

Hi
no chance to really optimise this with formulas. Maybe a pivot table is
better suited for your needs

 

[Aladin Akyurek]

Two possibly applicable tips...

Try to use SumIf formulas by concatenating appropriate fields in the data
area instead of Sumproduct formulas.

Try to apply SumProduct formulas to subranges of interest by computing the
subranges in the data area set in ascending order on an appropriate field.