Data base functions, allow you to perform operations on an Excel database that involve multiple fields. In a sense, they offer the power of array formulas, but generally make faster worksheets because they do not require the same amount of recalculation.

With database functions you can get counts, averages, or sums of your data based on selection criteria involving multiple fields. Implementing database functions is a little like implementing advanced filters. You have to establish a criteria range first before you use the function itself.

Take the following Excel database as an example.

We will use the DSUM database function to calculate the total profit for all Tuesdays and Thursdays with Sales grater than 3000 and Profit less than 4500.

The first step in using a database function is to set up a criteria range. We can set up a criteria range by entering the field headings that we want to use, exactly as they are in the original data list.

For this example, we will need the fields Day, Sales, and Profit for our criteria range. If we enter these field headings in cells F1, G1, and H21 respectively, we can then move on to setting up our criteria.

This is the criteria range for the DSUM database function. In the row under the field headings, we have T* >3000 <4500. This means that records with a Day starting with T and Sales greater than 3000 and Profit less than 4500 will be selected for summation. If you wanted to join the criteria with a logical or, you would offset the criteria on separate rows (like an advanced filter).

The next step is to choose a cell (H4) to place the function in. To enter the function, activate the cell and choose Insert->Function, or click the function (fx) button on the formula bar. In the insert function dialogue box, choose DSUM from the database category, to reveal the Function Arguments box.

Select your original list with your mouse, or enter the range directly into the Database data area. Press Tab or use your mouse to move to the Field data area and type Profit (this is the field that will be summed).

Put your curser in the Criteria data area, and select the entire criteria range (F1:H2) with your mouse. To finish, click the OK button.

You can see the sum of the Profit fields for records that meet the criteria in cell H4.

You could also activate cell H4 and entered the function directly into the formula bar.

=DSUM(A1:D25,"Profit",F1:H2)

The database range is A1:D25, the field that is being summed is Profit, and the criteria range is F1:H2.

If you enter a database function directly into the formula bar, remember to keep your cell ranges accurate, and put the field that will be summed, averaged, or otherwise operated on, in quotation marks.

Some other useful database functions are,

·              DAVERAGE, which is used to average a values in a field based on specified criteria

·              DCOUNT Counts the cells that contain numbers and meet the specified criteria

·              DMAX and DMIN return the largest and smallest values respectively from records that meet the specified conditions.

·              DPRODUCT multiplies values in a field according to specific conditions

·              DGET returns a single record value from a record that meets the specified conditions.

All of the data base functions use the same argument format, namely,

Function name(Database range, Field to be returned or calculated, Criteria range).

This Image shows the use of the DAVERAGE function. The cell that contains the function is F4, the database range is A1:D25, the field being averaged is Profit, and the criteria range is F1:F2.

The function itself is visible in the formula bar. Notice that the criterion in cell F2 is Week*. This means that the average will be calculated based on the weekly profit figures. (Week* can be Week1, Week2, Week3, and so on).