SUMIF has slowed down the workbook

[Jude]

I have created a worksheet which uses a large number of SUMIFs to create a
table (36 columns by 75 rows therefore 2700 SUMIFs) which picks the results
from a flat file over 16000 rows by 4 columns. The data is in columnD and the
variables in columnsA-C. The variables correlate to the table row and column
headings I have created and one other cell with a value chosen from a
drop-down. The workbook has slowed down considerably and takes a while to
recalculate each time I amend something else in the workbook.

I do not want to take off the automatic calculation and pivot tables are not
an ideal solution.

Does anyone have any advice as to how to speed things up?

 

[Charles Williams]

SUMIF is quite fast compared to other functions.

If the flatfile data does not change very often I would suggest sorting it
by columns A-C and then either writing a UDF that exploits the fact that the
data is sorted, or precalculate small contiguous subset ranges so that you
can either use SUM or SUMIF on much smaller ranges (SUM is faster than
SUMIF)

There is an example of this sort of approach at
http://www.decisionmodels.com/optspeedk.htm

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com