Sumif, dsum, query, something else? What's faster?
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

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

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you tried using PivotTables?

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com