By Paul McFedries

Part of Excel Data Analysis For Dummies Cheat Sheet

To help you analyze data that’s stored in a table or range, you can turn to Excel’s powerful database functions, which enable you to apply calculations such as sum, average, and standard deviation.

The database functions all use the same three arguments:

  • database: The range of cells that make up the table you want to work with. You can use either the table name or the table range address. If you go with the table name, be sure to reference the entire table by using the syntax Table[#All] (where Table is the name of your table).
  • field: A reference to the table column on which you want to perform the operation. You can use either the column header or the column number (where the leftmost column is 1, the next column is 2, and so on). If you use the column name, enclose it in quotation marks (for example, “Unit Price”).
  • criteria: The range of cells that hold the criteria you want to work with. You can use either a range name, if one is defined, or the range address.
Function What It Does
DGET(database, field, criteria) Retrieves a value from a table or range
DSUM(database, field, criteria) Adds values from a table or range based on the criteria you specify
DCOUNT(database, field, criteria) Counts the numeric values in a table or range that match the specified criteria
DCOUNTA(database, field, criteria) Counts the nonblank items in a table or range that match the specified criteria
DAVERAGE(database, field, criteria) Averages the values in a table or range that match the specified criteria
DMAX(database, field, criteria) Finds the maximum value in a table or range based on the criteria you specify
DMIN(database, field, criteria) Finds the minimum value in a table or range based on the criteria you specify
DPRODUCT(database, field, criteria) Multiplies the values in a table or range that match the specified criteria
DSTDEV(database, field, criteria) Calculates the standard deviation of the sample values in a table or range that match the specified criteria
DSTDEVP(database, field, criteria) Calculates the standard deviation of the population values in a table or range that match the specified criteria
DVAR(database, field, criteria) Calculates the variance of the sample values in a table or range that match the specified criteria
DVARP(database, field, criteria) Calculates the variance of the population values in a table or range that match the specified criteria