Speeding up caluclation of array formula

E

EstherJ

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
 
B

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
 
Top