Database functions

Syntax

Dfunction(Database, Field_str, Criteria)

Where Dfunction is one of the following:

  • DAVERAGE
  • DCOUNT
  • DCOUNTA
  • DGET
  • DMAX
  • DMIN
  • DPRODUCT
  • DSTDEV
  • DSTDEVP
  • DSUM
  • DVAR
  • DVARP

Type 

Database

Database functions are particularly handy when Google Sheets is used to maintain structured data, like a database. Each database function, Dfunction, computes the corresponding function on a subset of a cell range regarded as a database table. Database functions take three arguments:

  • Database is a range, embedded array or array generated by an array expression that is structured so that each row after Row 1 is a database record, and each column is a database field. Row 1 contains the labels for each field.
  • Field indicates which column (field) contains the values to be averaged. This can be expressed as either the field name (text string) or the column number, where the left-most column would be represented as 1.

  • Criteria is a range, embedded array or array generated by an array expression that is structured such that the first row contains the field name(s) to which the criterion (criteria) will be applied, and subsequent rows contain the conditional test(s).

The first row in criteria specifies field names. Every other row in criteria represents a filter, which is a set of restrictions on the corresponding fields. Restrictions are described using Query-by-Example notation, and can include a value to match or a comparison operator followed by a comparison value. An empty cell means no restriction on the corresponding field.

A filter matches a database row if all the filter restrictions (the restrictions in the filter's row) are met. A database row (record) satisfies criteria if and only if at least one filter matches it. A field name may appear more than once in the criteria range to allow multiple restrictions that apply simultaneously (for example, temperature >= 65 and temperature <= 82).

DGET is the only database function that doesn't aggregate values. DGET returns the value of the field specified in the second argument (similarly to a VLOOKUP) only when exactly one record matches criteria; otherwise, it returns an error indicating no matches or multiple matches.

Example spreadsheet with multiple database functions.

Was this helpful?
How can we improve it?