Excel Formula Efficiency 3: Database D Functions Are Fast

Tags:
Excel DSUM DAVERAGE DCOUNT Are Faster Than SUMPRODUCT Mike Gel Girvin Highline Community College
ExcelIsFun
  • Affiliate Submitter:
    ExcelIsFun
  • International International
  • Comments: 0
  • Views: 45
  • Added: 24-Feb-09

See how the Excel 2003, 2002, 2001, 1997 Database D functions (DSUM, DAVERAGE, DCOUNT) are much faster than SUMPRODUCT and SUM array formulas

If your data is in database Excel List Excel Table format, then Database D functions are much faster than SUMPRODUCTS or SUM array formulas.

Learn how to speed up slow calculating spreadsheets with formulas that calculate faster. See many methods to speed up your spreadsheet decrease the amount of time it takes for the formulas to calculate. The tricks you see are from a White Paper called Improving Performance in Excel 2007. In this video series you will see tricks for both Excel 2003 and Excel 2007. Many of the large formulas in this series involve multi-conditional multiple criteria calculations that slow down the spreadsheet (worksheet, workbook).

  1. Categories: Science & Tech
Comments on

Excel Formula Efficiency 3: Database D Functions Are Fast

5 Comments | Add Comment
  • I just love the way ...

    I just love the way when you make a funny sound from ur mouth..........aaaatttttttttaaaaaa akka bingo.
    Lolzzzzzzzz................

    By harmeet1421 [Affiliate User] 1252708948 Reply Spam Moderate Up Moderate Down
  • Dear ...

    Dear SoundOfTheSun29,
    #VALUE! errors mean that the arguments are not correct. DCOUNT must go like this:
    DCOUNTA(whole database including field names, name of field you want to count, name of field with criteria and criteria - 2 cells)
    For example: DCOUNT(A1:C10,"Products",D1:D2) where D1 has field name D2 has criteria and A1:C10 has whole database.
    If you watch the video carefully you will see something similar to what I wrote here.
    --excelisfun

    By ExcelIsFun [Affiliate User] 1239334322 Reply Spam Moderate Up Moderate Down
  • I don't get the ...

    I don't get the criteria portion of the function...I'm trying to reference a database on another sheet in which I need to count the number of products within the database. Bottom-line, I keep getting #VALUE. Suggestions?

    By SoundOfTheSun29 [Affiliate User] 1239332522 Reply Spam Moderate Up Moderate Down
  • Dear planiolro, ...

    Dear planiolro,

    Here's the rest of the comment:

    In Excel 2007, SUMIFS, COUNTIFS, AVERAGEIFS are fastest and do not require the data to be in a particular format, otherwise, double negative SUMPRODUCT is probably best.

    --excelisfun

    By ExcelIsFun [Affiliate User] 1235586135 Reply Spam Moderate Up Moderate Down
  • Dear planiolro, ...

    Dear planiolro,

    No way. D functions were around before Tables and Lists!!! D functions rock!

    If your data is set up with field names in first row (column headers) and records are in rows AND the calculation you are doing corresponds to one of the 12 D functions, D functions are the fastest way to do multiple condition calculations in earlier versions. If your calculation does not correspond to a D function, then the double negative SUMPRODUCT is probably best.
    --excelisfun

    By ExcelIsFun [Affiliate User] 1235586096 Reply Spam Moderate Up Moderate Down
5 Comments | Add Comment