Sumif, dsum, query, something else? What's faster?

codyswan

New Member
Joined
Mar 10, 2002
Messages
22
I have some very large files (60-100 meg) where I want to get counts, sums, and averages for many sets of multiple criteria. I've previously used dsums, but each dsum takes up two rows and thus copying and editing formulas isn't as convenient. The dsum variety takes about 2 hours to calculate thousands of different dsums on a dataset. What is a faster alternative? Will sumifs take about the same amount of time and just be easier to deal with?

Thanx
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Yes, I tried pivot tables. Unfortunately, many of the criteria have wildcard characters. One company may show up in the database as ABC Corp, ABC Corporation, ABC, ABC Inc, etc. The pivot tables treated them as different categories. Since I'm doing crosstabulations of at least two variables, I didn't see an efficient way of collapsing the categories.
 
Upvote 0
On 2002-03-28 10:47, codyswan wrote:
Yes, I tried pivot tables. Unfortunately, many of the criteria have wildcard characters. One company may show up in the database as ABC Corp, ABC Corporation, ABC, ABC Inc, etc. The pivot tables treated them as different categories. Since I'm doing crosstabulations of at least two variables, I didn't see an efficient way of collapsing the categories.

Create a Grouped item or "map" all these variants to one name in separate column of your Database.
 
Upvote 0
Hi codyswan

I find you problem very interesting, in particular the fact you say that the Database functions are very slow to recalculate. I use this functions a lot with worksheets containing thousands of rows by 20 or so columns and have never run into a speed issue. Are you sure you do not have other functions that would be causing this, array formulas are normally a prime cause.

You also mention that editing them is awkward. I find these functions extremely convenient for this, hence my reason for using them. They do take a bit longer to set up (which is why so many people avoid them) but the benefit far outweighs this.


I have a Workbook you can download here:
http://www.ozgrid.com/download/default.htm that shows how these functions can be used in a very convenient way.
"DFunctionsWithValidation.zip" is the Workbook.
 
Upvote 0
The database is about 40,000 records long by 25 columns. There are about 200,000 crosstabs to calculate on each database. Each cell in the crosstab has two conditions. Sometimes the conditions include wildcards.

I don't have any array formulas.

To answer certain questions, I have to go back in and write new dsums and dcounts.

I'm going to try pivot tables again with collapsed categories and see how long it takes.

thanks
 
Upvote 0
Hi codyswan:
You have already got some valuable insight from the masters here. Just a thought -- have you tried switching to MANUAL CALC, and calculate just what you need rather than calculate everything even if you want to calculate something very specific.
 
Upvote 0
Yes, Yogi, I've set the recalc to manual and only do a worksheet recalc (shift F9).

The pivottable is looking promising, but I'm running into problems with it having too many columns. Somewhere in there is a setting to get the pivottable to wrap.
 
Upvote 0
Hi codyswan:
How about going down to calculating at a range-level or even cell level. In the past when I was working with relatively large Lotus files, I would have CALC turned to manual, then I would force a calc on a cell by copying over itself. Excel does not facilitate that one can achieve that facility througn a one-liner in VBA.
 
Upvote 0
I have it set so that the formulas are text when I bring in data. Then, I removed the text marker and they become formulas. To get a recalc of an individual formula, I just delete edit the cell, put in and delete a space on the end and hit enter. It recalcs just that cell.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top