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 included in the result. 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.