DSUM function and spreadsheet calculation speed?

[JIM456]

Hi...

I have a relatively small spreadsheet, however, it has a number of DSUM forumlas which are each looking at 20,000 rows and about 55 columns. This seems to be bogging down the calculation speed such that, even with virtually no data, the calculation time is about 30 seconds on a PC with 4GB RAM and an i7 processor! Any ideas on what type of formula I could use instead of the DSUM, or how I could possibly speed up the DSUM (which is really the best formula for this circumstance)?

Thanks Much,


Jim

 

[ukmikeb]

Hi...

I have a relatively small spreadsheet, however, it has a number of DSUM forumlas which are each looking at 20,000 rows and about 55 columns. This seems to be bogging down the calculation speed such that, even with virtually no data, the calculation time is about 30 seconds on a PC with 4GB RAM and an i7 processor!

Is your database definition 20,000 rows and 55 columns?

Are all the rows occupied? The highlighted comment suggests otherwise.

How many columns are used in the criteria?

Perhaps you could list a few of the formulae along with the respective criteria definitions.

Maybe we could then suggest a solution.

 

[JIM456]

Hi Mike..thanks for taking a look at this. Yes, we're using 20,000 rows by 55 columns or 1.1M cells. The criteria ranges use either 2 or 3 columns.

Here's a formula example:

=DSUM(DIRECT!$B$7:$BF$20000,11,$A$101:$K$107) ...where "DIRECT" is another tab with the database on it

Here's the criterial range:

eList Division Segment Proj. No. Program Name Category Contract Type Booking Type Data Fields Prior ITD
=C&D =Labor Onsite - Group
=C&D =Labor Onsite - TechOps
=C&D =Labor Onsite - Group SCIC
=C&D =Labor Onsite - TechOps SCIC
=C&D =Labor Onsite - Group: BusOps
=C&D =Labor Onsite - Group SCIC: BusOps

 

[ukmikeb]

In the database definition you're referencing 57 columns and your sum column is column 11.

Would it be worth limiting the database definition to those 11 columns assuming the criteria fields also reside in the first 11 columns?

Just thought I'd mention that as I predominantly use DCount and my database definition is 13 columns but I only reference six of those columns in the criteria fields.

 

[JIM456]

Unfortunately, the sum column changes in such that there is a different sum column for 36 different columns.

 

[ukmikeb]

What I was trying to suggest was that you might get a performance improvement by limiting the columns referenced, both for the database and the criteria.
Applying meaningful named ranges to the limited selections could be a hint as to the purpose of each DSUM.

This video :-
Excel Formula Efficiency 12: Fastest Formula In Excel - YouTube
gives some stats comparing use of different formulae, which you could find useful.

Good luck.