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 syntaxTable[#All](whereTableis 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(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Retrieves a value from a table or range |
DSUM(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Adds values from a table or range based on the criteria you specify |
DCOUNT(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Counts the numeric values in a table or range that match the specified criteria |
DCOUNTA(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Counts the nonblank items in a table or range that match the specified criteria |
DAVERAGE(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Averages the values in a table or range that match the specified criteria |
DMAX(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Finds the maximum value in a table or range based on the criteria you specify |
DMIN(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Finds the minimum value in a table or range based on the criteria you specify |
DPRODUCT(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Multiplies the values in a table or range that match the specified criteria |
DSTDEV(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Calculates the standard deviation of the sample values in a table or range that match the specified criteria |
DSTDEVP(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Calculates the standard deviation of the population values in a table or range that match the specified criteria |
DVAR(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Calculates the variance of the sample values in a table or range that match the specified criteria |
DVARP(<em>database</em>, <em>field</em>, <em>criteria</em>) |
Calculates the variance of the population values in a table or range that match the specified criteria |
