Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF has slowed down the workbook
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF has slowed down the workbook
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 "Jude" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF to clsoed workbook | Excel Discussion (Misc queries) | |||
Why has my mouse pointer response slowed to a crawl speed ? | Excel Worksheet Functions | |||
Sumif across workbook tabs | Excel Worksheet Functions | |||
with SP2, has the rand() function been slowed down ? | Excel Discussion (Misc queries) | |||
I need to use the sumif for a workbook, not a worksheet | Excel Worksheet Functions |