Excel functions / Database
DSUM function
Adds up a column of a table for the rows that meet conditions written in cells.
Syntax
=DSUM(database, field, criteria)
Arguments
database: The table, with its column labels in the first row.field: The column to work on: its label in quotes ("Profit") or its number counting from 1.criteria: The conditions: a range with column labels in its first row and what to match below them.
When to use it
DSUM adds up one column of a table for the rows that meet conditions, like SUMIFS. The difference is where the conditions live: in a small range of cells, with the column labels above them, instead of inside the formula.
That makes the conditions something a person can change by typing in a cell. Conditions on the same row must all be true; a second row of conditions is an alternative.
Examples
North, and more than 500
A B C D E 1 Region Sales 2 North >500 2100 3 4 Region Rep Sales 5 North Ana 1200 6 South Ben 800 7 North Chloe 900 8 West Ana 400 9 North Ana 300 In E2:
=DSUM(A4:C9, "Sales", A1:B2)→ 2100The criteria range is A1:B2: two labels, and one condition under each.
One condition
A B C D E 1 Region Sales 2 North >500 2400 3 4 Region Rep Sales 5 North Ana 1200 6 South Ben 800 7 North Chloe 900 8 West Ana 400 9 North Ana 300 In E2:
=DSUM(A4:C9, "Sales", A1:A2)→ 2400North or West: a second row of criteria
A B C D E 1 Region 2 North 2800 3 West 4 Region Rep Sales 5 North Ana 1200 6 South Ben 800 7 North Chloe 900 8 West Ana 400 9 North Ana 300 In E2:
=DSUM(A4:C9, "Sales", A1:A3)→ 2800
Common mistakes
- A label that does not match. The labels in the criteria range must be spelled like the headings of the table.
- An empty row in the criteria. A row of criteria with nothing in it matches everything. Make the criteria range only as tall as the conditions.
- Text matches the start. A condition of North also matches Northwest. For an exact match, type ="=North" in the cell.
Common questions
What is the difference between DSUM and SUMIFS?
Both add up rows that meet conditions. SUMIFS takes its conditions as arguments in the formula; DSUM reads them from a range of cells, and can combine them with OR by using more than one row.
DSUM in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: DSUMA
- Danish: DSUM
- Dutch: DBSOM
- Finnish: TSUMMA
- French: BDSOMME
- German: DBSUMME
- Hungarian: AB.SZUM
- Italian: DB.SOMMA
- Norwegian: DSUMMER
- Polish: BD.SUMA
- Portuguese (Brazil): BDSOMA
- Portuguese (Portugal): BDSOMA
- Russian: БДСУММ
- Spanish: BDSUMA
- Swedish: DSUMMA
- Turkish: VSEÇTOPLA
Related functions
- SUMIFS: Adds the cells that meet every one of several conditions.
- DAVERAGE: The average of a column of a table for the rows that meet conditions written in cells.
- DCOUNT: Counts the numbers in a column of a table for the rows that meet conditions written in cells.
- DGET: The one value in a column of a table from the single row that meets the conditions. Two matching rows are an error, which makes it a strict lookup.
- SUBTOTAL: Totals a range, leaving out rows that a filter hides and other subtotals inside it.