DSUM Speed

[dalameda71]

I need to find a way to speed up the calculations of DSUMs. I have created workbook that has two different tabs with downloaded data from a system query. The DSUMs, based on three criteria, review that downloaded data and show the results. I have 5 tabs full of DSUM formulas. Each tab as about 200 rows and 14 columns of DSUM formulas.
DSUM(MOVEMENTS!$G:$V,3,$A$86:$C96)-SUM(K$87:K95) (the last SUM part removes the results from the cells above, i do this so i dont have to have the criteria at the top of each row...for example, first DSUM uses criteria 1, second DSUM uses criteria 1 & 2 and then subtracts out criteria 1).
problem is that it takes 15-20 minutes to refresh.

Any suggestions??

 

[Aladin Akyurek]

I need to find a way to speed up the calculations of DSUMs. I have created workbook that has two different tabs with downloaded data from a system query. The DSUMs, based on three criteria, review that downloaded data and show the results. I have 5 tabs full of DSUM formulas. Each tab as about 200 rows and 14 columns of DSUM formulas.
DSUM(MOVEMENTS!$G:$V,3,$A$86:$C96)-SUM(K$87:K95) (the last SUM part removes the results from the cells above, i do this so i dont have to have the criteria at the top of each row...for example, first DSUM uses criteria 1, second DSUM uses criteria 1 & 2 and then subtracts out criteria 1).
problem is that it takes 15-20 minutes to refresh.

Any suggestions??

The set up [ DSUM()-SUM() ] looks familiar...

Try also the TABLE approach as described in:

http://support.microsoft.com/default.aspx?scid=kb;en-us;282851

If this also fails qua performance, you might want to switch to the pivot table approach.

 

[dalameda71]

Thanks for the reply Aladin. Unfortunately if I use the table method it will make it difficult for troubleshooting for the users of the report who don't understand Excel as well so i am a little hesitant to do it that way. I am also hesitant on using pivot tables because the file size of this report is already 5MB and when you add a pivot table it almost always increases the size of the file by a couple MB. I will try both though and see how well i feel they work.