Excel Formula Efficiency 3: Database D Functions Are Fast

[Water85128]

very good ! love it

[ExcelIsFun]

I am glad that they help!

[robinmitra1]

Great tutorial! Thanks!

BTW, the Average could have been calculated simply as Sum/Count.

[6642633]

WOW...

I never used the D functions b4...

and never thought they r so easy and fast!

Thanks a ton

[ExcelIsFun]

You are welcome!

[ChazmaniacTV]

Awesome video, a great help.

[ExcelIsFun]

I am glad that they help!

[thuynguyenable]

After I watch this video about Database D Functions, I created the same table to practise. But after that I did a unique list for each criteria column and then create a drop-down list for each criteria column (SalesRep, Customer, Product). And then D Formulas gives me different results when I change any criteria in the drop down list. Really cool trick, help me work faster. Thanks ExcellsFun.

[ExcelIsFun]

You are welcome!!

[harmeet1421]

I just love the way when you make a funny sound from ur mouth..........aaaatttttttttaa­aaaa akka bingo.

Lolzzzzzzzz................

[ExcelIsFun]

Yes, the funny sounds makes Excel more fun!!!

[SoundOfTheSun29]

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?

[ExcelIsFun]

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

[planiolro]

I was expecting that in order to use database function it is mandatory to have a database which according to my understanding was to have a table in 2007 or a list in 2003. But apparently I am wrong. Highlighting is enough.

[ExcelIsFun]

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

[ExcelIsFun]

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