Aggregating tabular business data based on multiple conditional rules is a standard requirement in spreadsheet modelling. Analysts traditionally combine multiple SUMIFS, AVERAGEIFS, or nested FILTER formulas to evaluate complex criteria. While functional, SUMIFS formulas can become unreadable when filtering across numerous attributes, and incorporating OR logic across different columns often requires adding multiple SUMIFS formulas together. To streamline complex multi-condition calculations, Google Sheets provides a dedicated suite of Database Functions, led by DSUM and DAVERAGE. These functions evaluate structured tabular data against clean, visual criteria ranges without complex nested code.
Understanding Database Function Architecture
Google Sheets Database functions follow a uniform, highly readable three-argument syntax:
Formula Syntax
DSUM(database, field, criteria)
DAVERAGE(database, field, criteria)The components of these functions operate as follows:
database: The entire tabular dataset including column header labels (e.g.A1:E50).field: The specific column you wish to calculate. You can supply the exact textual header name in quotes (e.g."Revenue") or its 1-indexed column number (e.g.4).criteria: A dedicated cell range containing the column headers and condition rules you want to match.
Setting Up the Criteria Range
The secret to mastering database functions lies in constructing the criteria range. A criteria range consists of header labels that match your database columns, with conditional filter rules placed directly underneath:
- AND Logic (Same Row): Placing criteria on the same horizontal row requires all conditions to be met simultaneously.
- OR Logic (Different Rows): Placing criteria on different rows evaluates as an OR condition (either rule can match).
Practical Example: Sales Analysis
Suppose you maintain an equipment sales register in cells A1:D15:
- Column
A: Department (e.g. Industrial, Commercial) - Column
B: Region (e.g. North, South) - Column
C: Units Sold (e.g. 45, 120) - Column
D: Total Sales (e.g. 15,000, 36,000)
Example 1: Calculating Total Sales for a Region and Department (AND Logic)
Set up a criteria block in cells F1:G2:
| Department (F1) | Region (G1) |
|---|---|
| Industrial | North |
In cell F4, calculate the total sales using DSUM:
=DSUM(A1:D15, "Total Sales", F1:G2)Google Sheets scans the database, matches rows where Department is Industrial AND Region is North, and sums the corresponding Total Sales.
Example 2: Calculating Average Sales with Comparison Operators
Criteria ranges support comparison operators (>, <, >=, <=, <>). To compute the average sales for orders where Units Sold exceeds 50:
Set cell F1 to Units Sold and cell F2 to >50. Enter the formula:
=DAVERAGE(A1:D15, "Total Sales", F1:F2)Example 3: Complex OR Logic Across Multiple Columns
Suppose you want to sum sales for either Industrial orders in the North OR any orders in the South where sales exceed 20,000:
| Department (F1) | Region (G1) | Total Sales (H1) |
|---|---|---|
| Industrial | North | |
| South | >20000 |
Execute the query:
=DSUM(A1:D15, "Total Sales", F1:H3)Executing this query with standard SUMIFS would require writing multiple formulas and manually subtracting overlap. With DSUM, it evaluates in a single, transparent formula.
Key Advantages Over IFS Functions
- Auditable and Visual: Business stakeholders can inspect and modify filter criteria directly in worksheet cells without editing formula syntax.
- Effortless OR Filtering: Stacking criteria rows effortlessly handles complex combinatorial logic.
- Uniform Sister Functions: Once your criteria table is built, you can easily deploy
DCOUNT,DMAX, andDMINusing the identical criteria block.