Establish the Criteria Area in an Excel Database Function - dummies

Establish the Criteria Area in an Excel Database Function

By Ken Bluttman

The criteria of a database function are not part of the database function arguments but are somewhere in the worksheet and then referenced by the function. The criteria area can contain a single criterion, or it can contain two or more criteria. Each individual criterion is structured as follows:

  • In one cell, enter the field name (header) of the database column that the criterion will apply to.

  • In the cell below, enter the value that the field data must meet.

The figure shows the student database with a criteria area to the right of the database. There are places to put criteria for the Class, Teacher, and Final Grade. In the example, a criterion has been set for the Class field. This criterion forces the database function to process only records (rows) where the Class is Accounting 101. Note, though, that a criterion can be set for more than one field. In this example, the Teacher and Final Grade criteria have been left blank so they don’t affect the results.

Selecting criteria to use with a database function.
Selecting criteria to use with a database function.

The DAVERAGE function has been entered into cell F8 and uses this criteria range. The three arguments are in place. The name Students tells the function where the database is, the Final Grade field (column) is where the function finds values to calculate the average, and the criteria are set to the worksheet range that has criteria that tell the function to use only records where the Class is Accounting 101 — in other words, F2:H3. The entry in cell F8 looks like this:

=DAVERAGE(Students,"Final Grade",F2:H3)

Why does this function refer to F2:H3 as the criteria range when the only defined criterion is located in the range F2:F3? It’s a matter of convenience. Because cells G3 and H3 in the criteria range are blank, the Teacher and Final Grade fields are ignored by a database function that uses this criteria range. However, if you want to enter a criterion for one of those fields, just enter it in the appropriate cell; there is no need to edit the database function arguments. What about assigning a name to the criteria area and then using the name as the third argument to the database function? That works perfectly well, too.

Whether you use a named area for your criteria or simply type the range address, you must be careful to specify an area that includes all the criteria but does not include any blank rows or columns. If you do, the database function’s results will be incorrect.

Here’s how you enter any of the database functions. This example uses the DSUM function, but the instructions are the same for all the database functions; just use the one that performs the desired calculation. Follow these steps:

  1. Import or create a database of information in a worksheet.

    The information should be in contiguous rows and columns. Be sure to use field headers.

  2. Optionally, use the New Name dialog box to give the database a name.

  3. Select a portion of the worksheet to be the criteria area and then add headers to this area that match the database headers.

    You have to provide criteria headers only for database fields that criteria are applied to. For example, your database area may have ten fields, but you need to define criteria to three fields. Therefore, the criteria area can be three columns wide.

  4. Position the cursor in the cell where you want the results to appear.

    This cell must not be in the database area or the criteria area.

  5. Type =DSUM( to begin the function entry.

  6. Enter the database range or a name, if one is set.

  7. Type a comma (,).

  8. Enter either of the following:

    • The header name, in quotation marks, of the database field that the function should process

    • The column number

  9. Type a comma (,).

  10. Enter the range of the criteria area.

  11. Type a ) and press Enter.