Database Functions

DATABASE FUNCTIONS

Sorry- I just finished baking an apple pie so I decided to use it as my example.

Database functions operate similar to other Excel functions.

These database functions can be very effective and could easily be used in lieu of sumif or countif if you have Excel 2003 and even Sumifs and Countifs in Excel 2007.

The syntax is =FieldName (database range, field, criteria).
The database range is the entire range of data including headings.
The field is the column that you wish to perform the function on.
The criterion is the range of cells that you are testing to see if they meet specific conditions.

Some of the more common Database Functions are:

=DSUM (database range, field, criteria)
=DCOUNT (database range, field, criteria)
=DAVERAGE (database range, field, criteria)

To use Database functions you need to have set up a separate criteria area either above or below your data where you specify what you are testing for.

Below is an example of how the DSUM function would be used. Here my database range is A9.F19, which includes your headings.

I want it to sum up Total Sales which is in Column F so my field is F9.

My criteria is at rows A24.B26 and specifies all Apple pies have a cost exceeding $1.80 and all Peach pies that have a cost exceeding $1.60.

So, =DSUM(A9:F19, F9,A24.B26) is saying add up Total Sales for all apple pies that have a cost greater than $1.80 and all peach pies that have a cost greater than $1.60

It is important to remember that the headings on the criteria at row A24 exactly match the column headings at row 9 so you may want to copy them to make sure they are set up the same.

It is also important to understand that Excel reads each row of the criteria as an OR statement.

(My data does not have to be organized by pie name- I just did that so it was easier for you to see.)