SUMIF has slowed down the workbook

J

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?
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top