Using Database Functions

To create formulas that return results based on a criteria range, use Excel's database worksheet functions. These functions all begin with the letter D, and they are listed in the Database category of the Insert Function dialog box.

Table 9-4 lists Excel's database functions. Each of these functions operates on a single field in the database.

Table 9-4: EXCEL'S DATABASE WORKSHEET FUNCTIONS
Open table as spreadsheet

Function	Description
DAVERAGE	Returns the average of database entries that match the criteria
DCOUNT	Counts the cells containing numbers from the specified database and criteria
DCOUNTA	Counts nonblank cells from the specified database and criteria
DGET	Extracts from a database a single field from a single record that matches the specified criteria
DMAX	Returns the maximum value from selected database entries
DMIN	Returns the minimum value from selected database entries
DPRODUCT	Multiplies the values in a particular field of records that match the criteria in a database
DSTDEV	Estimates the standard deviation of the selected database entries (assumes that the data is a sample from a population) of selected database entries
DSTDEVP	Calculates the standard deviation of the selected database entries, based on the entire population of selected database entries
DSUM	Adds the numbers in the field column of records in the database that match the criteria
DVAR	Estimates the variance from selected database entries (assumes that the data is a sample from a population)
DVARP	Calculates the variance, based on the entire population of selected database entries

The database functions all require a separate criteria range, which is specified as the last argument for the function. The database functions use exactly the same type of criteria range as discussed earlier in "Specifying Advanced Filter Criteria."

See Figure 9-25. The formula in cell B24, which follows, uses the DSUM function to calculate the sum of values in a table that meet certain criteria. Specifically, the formula returns the sum of the Sales column for records in which the Month is Feb and the Region is North.

Figure 9-25: Using the DSUM function to sum a table using a criteria range.

 =DSUM(B6:G21,F6,Criteria) 

In this case, B6:G21 is the entire table, F6 is the column heading for Sales, and Criteria is the name for B1:C2 (the criteria range).

Following is an alternative version of this formula that uses structured table references:

 =DSUM(Table1[#All],Table1[[#Headers],[Sales]],Criteria) 

On the CD 

This workbook is available on the companion CD-ROM. The filename is  database formulas.xlsx.

Note 

You may find it cumbersome to set up a criteria range every time you need to use a database function. Fortunately, Excel provides some alternative ways to perform conditional sums and counts. Refer to Chapter 7 for examples that use SUMIF, COUNTIF, and various other techniques.

If you're an array formula aficionado, you might be tempted to use a literal array in place of the criteria range. In theory, the following array formula should work (and would eliminate the need for a separate criteria range). Unfortunately, the database functions do not support arrays, and this formula simply returns a #VALUE! error.

 =DSUM(B6:G21,F6, {"Month","Region";"Feb","North"})