DSUM totals a field in a table using a separate criteria block. Conditions on the same criteria row combine with AND; alternative criteria rows combine with OR.
DSUM function syntax
=DSUM(database, field, criteria)- database: range including its header row.
- field: header label or one-based column index within the database.
- criteria: header row followed by one or more condition rows.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Region | Person | Sales |
|---|---|---|
| East | Alex | 250 |
| West | Blair | 150 |
| East | Casey | 300 |
| North | Drew | 180 |
| West | Eden | 220 |
| East | Fran | 80 |
Total sales for one region
Enter this formula in A10. The inline criteria array is a two-row block: Region above East. You can enter those two cells in a separate area and reference them instead.
=DSUM(A1:C7,"Sales",{"Region";"East"})Result: 630.
Use a numeric threshold
The criterion is text containing an operator and threshold. It keeps 250, 300 and 220. When entering a criterion cell, type >200 without an initial equals sign.
=DSUM(A1:C7,"Sales",{"Sales";">200"})Result: 770.
Require two conditions together
The criteria occupy two columns on the same row. Both East and sales above one hundred must match, excluding the East sale of eighty.
=DSUM(A1:C7,"Sales",{"Region","Sales";"East",">100"})Result: 550.
Accept either of two regions
East and West appear on separate criteria rows. A record that matches either row contributes to the total. North is excluded.
=DSUM(A1:C7,"Sales",{"Region";"East";"West"})Result: 1000.
Choose a field by its position
Sales is the third column inside A1:C7. The field position is relative to the database range, not necessarily the worksheet’s column number.
=DSUM(A1:C7,3,{"Region";"North"})Result: 180.
Put editable criteria in worksheet cells
Enter Region in G1 and =East as text in G2. Prefix the entry with an apostrophe to prevent Sheets treating it as a formula. The formula below uses that criteria block.
=DSUM(A1:C7,"Sales",G1:G2)Result: 630.
Other Google Sheets articles you may also like