Speeding up caluclation of array formula

[Guest]

I have a large sales analysis spreadsheet, which looks up the sales value for
a customer by year, quarter, month, sales rep by using formulas such as the
one below. It is taking a long time to calculate any changes. Is there
anything I can do to speed it up.


=SUM(($A9='MASTER DATA'!$A$2:$A$2500)*(F$5='MASTER
DATA'!$N$2:$N$2500)*(F$4='MASTER DATA'!$M$2:$M$2500)*(F$6='MASTER
DATA'!$L$2:$L$2500)*(F$7='MASTER DATA'!$F$2:$F$2500)*'MASTER
DATA'!$G$2:$G$2500)

Thanks,

Esther

 

[Bernie Deitrick]

Ester,

Try using pivot tables instead of monster array formulas. Filtering might
also work, using the SUBTOTAL function, which only acts on visible cells.

HTH,
Bernie
MS Excel MVP