DSUM Function

Adds up a column for the records that match your criteria.

Category

Database

Available in

All versions, Microsoft 365

Returns

A number

Want to practice DSUM in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

DSUM totals one column of a list, but only for the rows that meet conditions you type into a small criteria range on the sheet.

Because the conditions live in cells, changing a report is as easy as typing a different region or threshold — no editing of the formula.

Syntax

=DSUM(database, field, criteria) Explain

Arguments

ArgumentRequired?What it does
databaseYesThe whole list, including the header row — e.g. A1:E7. Each row is a record, each column a field.
fieldYesWhich column to calculate: its header in quotes ("Sales"), a cell holding the header, or its position (5 = the fifth column).
criteriaYesA small range with at least one header copied exactly from the list and the conditions underneath, e.g. G1:G2.

Examples

Example 1 — Total sales for one region

The criteria range G1:G2 says Region = North.

G5fx=DSUM(A1:E7,"Sales",G1:G2)
ABCDEFG
1RegionRepProductUnitsSalesRegion
2NorthAmaraPaper40520North
3SouthBenToner12576
4NorthChloeToner8384North sales
5EastDevPaper253251024
6NorthAmaraPens100120
7SouthEllaPaper30390
=DSUM(A1:E7,"Sales",G1:G2) Explain

Example 2 — Two conditions together (AND)

North orders with more than 20 units: both conditions sit on the same row.

G5fx=DSUM(A1:E7,"Sales",G1:H2)
ABCDEFGH
1RegionRepProductUnitsSalesRegionUnits
2NorthAmaraPaper40520North>20
3SouthBenToner12576
4NorthChloeToner8384Sales
5EastDevPaper25325640
6NorthAmaraPens100120
7SouthEllaPaper30390
=DSUM(A1:E7,"Sales",G1:H2) Explain

Example 3 — Either condition (OR)

North or East: put each alternative on its own row.

G6fx=DSUM(A1:E7,"Sales",G1:G3)
ABCDEFG
1RegionRepProductUnitsSalesRegion
2NorthAmaraPaper40520North
3SouthBenToner12576East
4NorthChloeToner8384
5EastDevPaper25325Sales
6NorthAmaraPens1001201349
7SouthEllaPaper30390
=DSUM(A1:E7,"Sales",G1:G3) Explain

Example 4 — A range between two values

Repeat the header to apply two conditions to the same column: Sales from 300 to 500.

G5fx=DSUM(A1:E7,"Sales",G1:H2)
ABCDEFGH
1RegionRepProductUnitsSalesSalesSales
2NorthAmaraPaper40520>=300<=500
3SouthBenToner12576
4NorthChloeToner8384Sales
5EastDevPaper253251099
6NorthAmaraPens100120
7SouthEllaPaper30390
=DSUM(A1:E7,"Sales",G1:H2) Explain

Common mistakes

  • #VALUE! or a total of 0. The field name or a criteria header doesn't match the list's header exactly — a stray space is enough. Copy the header rather than retyping it.
  • A blank row inside the criteria range. An empty criteria row means “no condition”, so every record is included. Make the criteria range only as tall as the conditions.

Notes

  • Criteria rules: conditions on the same row must all be true (AND); conditions on separate rows are alternatives (OR). Text such as Nor matches anything that begins with it — type ="=North" for an exact match. Use >500, <>East, or wildcards like *Paper*.
  • In Microsoft 365, SUMIFS, COUNTIFS, AVERAGEIFS and FILTER do the same jobs with the conditions written in the formula. The database functions shine when you want the conditions visible on the sheet, where anyone can change them.
  • Related functions: DSUM, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.