Excel DSUM & DCOUNT — Sum and Count Rows That Match a Criteria Table

TL;DR — DSUM(database, field, criteria) totals one column, but only the rows that match a criteria range — a small block of cells whose top row repeats your column headers and whose rows below spell out the conditions. =DSUM(A1:E200, "Amount", H1:H2) sums Amount for every row that matches whatever you put in H1:H2. DCOUNT is the same shape but counts the numeric cells in the field (use DCOUNTA to count text too). Two rules carry the whole family: the database argument must include the header row, and in the criteria block same row = AND, stacked rows = OR. The #1 bug: a criteria header that doesn't match a database header exactly matches nothing and quietly returns 0. Works in every Excel version.

=DSUM(A1:E200, "Amount", H1:H2)      ' total Amount for rows matching the criteria in H1:H2
=DCOUNT(A1:E200, "Amount", H1:H2)    ' how many matching rows have a NUMBER in Amount
=DCOUNTA(A1:E200, "Rep", H1:H2)      ' count matching rows that have a Rep filled in (text ok)

Most "sum with a condition" work in Excel is now done with SUMIFS — conditions typed straight into the formula. The D-functions answer the same question a different way, and that difference is the whole point: you write the conditions as a visible table on the sheet and point the formula at it. That sounds clunky until you hit a question with three OR-branches and a computed threshold — at which point a block of cells a reviewer can read beats a formula they have to decode.

What you'll learn

The mental model: the criteria range is your query, written on the grid
The three arguments — and why database must include the header row
How the criteria block encodes AND (same row) and OR (stacked rows)
Why DCOUNT returns 0 on a text column, and when to reach for DCOUNTA
The "empty criteria row matches everything" trick — and trap
The judgment call: when DSUM beats SUMIFS, and when it doesn't

The mental model: your query lives on the sheet

Every D-function reads three things: a table of data, the column you want a number from, and a criteria range. That last one is the idea nothing else in Excel uses. Instead of burying conditions inside the formula, you build a tiny table — one row of headers that match your data, one or more rows of conditions underneath — and hand its address to the function.

' The data (A1:E200) has headers: Region | Product | Rep | Units | Amount
' The criteria block (H1:H2):
'   H1: Region      <- a header copied from the data, exactly
'   H2: West        <- the condition
=DSUM(A1:E200, "Amount", H1:H2)      ' -> total Amount where Region = West

Read it as a sentence: from this table, sum the Amount column, for the rows that satisfy the little table in H1:H2. The power — and every pitfall — comes from that criteria block being data, not code. You can edit it, a reviewer can see exactly what was asked, and you can point ten different formulas at the same block. But because it's matched by header text, a typo there fails silently.

The three arguments — and the header row is mandatory

=DSUM(database, field, criteria). All three are required for real work, and the first one catches people immediately:

database — the full table including its header row (A1:E200, not A2:E200). The headers aren't decoration; they're how field and the criteria block find their columns. Start at row 2 and you get #VALUE! or a silently wrong column.
field — which column to crunch. Three ways to name it, in order of how well they survive edits:

=DSUM(A1:E200, "Amount", H1:H2)   ' by header text (quoted) — clearest, moves with the column
=DSUM(A1:E200, E1, H1:H2)         ' by a cell holding the header — good for parameterised reports
=DSUM(A1:E200, 5, H1:H2)          ' by column number (5th) — brittle; breaks if columns move

criteria — the range holding your header-plus-conditions block (H1:H2). It lives outside the database, usually a few columns to the right or a few rows above.

Prefer the quoted header ("Amount"). It reads like English and keeps working when someone inserts a column and turns the 5th field into the 6th.

The criteria block: AND across, OR down

This is the skill the whole family shares, so it's worth getting exactly right. The criteria range is a mini-table. Its top row is headers; each row beneath is a set of conditions. Conditions on the same row must all be true (AND); each additional row is an alternative (OR).

' AND — one row: Region West AND Amount over 1000
'   H1: Region    I1: Amount
'   H2: West      I2: >1000
=DSUM(A1:E200, "Amount", H1:I2)

' OR — stacked rows: Region West OR Region East
'   H1: Region
'   H2: West
'   H3: East
=DSUM(A1:E200, "Amount", H1:H3)

Widen the criteria range to include every column and row your block uses — for the OR example that's H1:H3, for the AND example H1:I2. The operators (>, <, >=, <>) go straight into the cell as text: >1000, <>West. Text criteria are case-insensitive and match from the start by default — a criterion of North also matches Northwest. To force an exact match, type ="=North". Wildcards work too: Jo* matches Jones and Johnson.

The single most common failure on this page: the criteria header must match a database header character for character. Ammount, Region (trailing space), or region vs Region — Excel doesn't warn, it just matches no rows and returns 0. When a D-function returns 0 or a total that's too low, check the header spelling first.

DCOUNT counts numbers; DCOUNTA counts everything

DCOUNT and DCOUNTA are the COUNT/COUNTA split, transplanted onto a criteria range — and the distinction bites exactly the same way.

=DCOUNT(A1:E200, "Amount", H1:H2)    ' counts rows where Amount is a NUMBER — e.g. 34
=DCOUNT(A1:E200, "Rep", H1:H2)       ' counts numeric cells in Rep -> 0 (Rep is text!)
=DCOUNTA(A1:E200, "Rep", H1:H2)      ' counts rows where Rep is non-blank -> 34

DCOUNT only counts cells that hold a number in the chosen field. Point it at a text column like Rep and it dutifully returns 0 — not an error, just a zero that looks like "no matches." When you want how many matching records exist regardless of content, use DCOUNTA against any always-filled column, or omit the field argument entirely: =DCOUNT(A1:E200, , H1:H2) counts every matching row. The rule of thumb: counting a number → DCOUNT; counting records → DCOUNTA.

"Match everything": the empty criteria row

Give a criteria block a header and a blank cell beneath it, and that condition matches every row. It's how you total an entire column through a D-function, and it's occasionally handy for a criteria block you toggle by typing a value in or clearing it out.

'   H1: Region
'   H2:            <- blank: no restriction
=DSUM(A1:E200, "Amount", H1:H2)      ' -> the grand total of Amount, all regions

The trap is the flip side: an accidental blank row inside a multi-row OR block widens the whole query to "everything," because that empty row matches all rows. If a DSUM suddenly returns the grand total when you expected a slice, look for a stray blank condition row in the criteria range.

The judgment call: DSUM vs SUMIFS

For most conditional totals, SUMIFS is the right default — it's compact, portable, and needs no helper block on the sheet. So when is DSUM the better tool? When the question itself should be visible and editable:

Complex OR logic. SUMIFS has no native OR; you stack it inside SUM({…}) or add formulas. DSUM does OR by adding a row — readable at a glance.
A question users will change. A criteria block is a little control panel. Non-formula colleagues can edit West to East, or >1000 to >5000, without touching a formula.
Formula-based conditions. DSUM accepts a criterion like =E2>AVERAGE(E:E) — "above the average order" — computed live. (More on that in the database functions guide.)
Auditability. In a financial model, a visible criteria table is documentation. The reviewer reads the question, not a nested formula.

Keep SUMIFS for the everyday "put this one conditional total in this cell," and for anything you'll copy down a column. Reach for DSUM when the criteria deserve to live on the sheet — and when you need the exactly-one-record extraction that only DGET provides.

How ExcelMaster helps

The friction with D-functions is never the DSUM — it's building the criteria block correctly: headers that match the data, AND on one row, OR down the rows, the range sized to cover it all. Tell ExcelMaster "total Amount for West or East orders over 1,000" and it lays out the criteria table, spells the headers to match your data, and writes the =DSUM(...) pointing at it. Paste a DSUM that stubbornly returns 0 and it checks the usual suspect — a header typo — and fixes it.

Frequently asked questions

What is the difference between DSUM and SUMIFS?

Both total a column conditionally. SUMIFS takes its conditions as arguments inside the formula; DSUM reads them from a criteria range on the sheet — a block of header cells and condition cells. DSUM handles OR logic and formula-based criteria more readably and keeps the question visible for review; SUMIFS is more compact and portable. Use SUMIFS by default, DSUM when the criteria should be editable on the grid.

Why does my DSUM return 0?

Almost always a mismatch between the criteria header and the database header. They must be identical — same spelling, no trailing spaces. Also confirm the database argument includes the header row (A1:E200, not A2:E200) and that the criteria range covers all your condition cells. A blank condition cell, by contrast, matches everything rather than nothing.

What is the difference between DCOUNT and DCOUNTA?

DCOUNT counts cells in the chosen field that contain a number; DCOUNTA counts cells that are non-blank, including text. Point DCOUNT at a text column and it returns 0. To count how many records match the criteria regardless of content, use DCOUNTA on a filled column, or omit the field argument.

How do I write an OR condition in a D-function?

Put each alternative on its own row of the criteria range. For "Region West OR East," place the header Region in one cell, then West and East in the two cells directly below it, and size the criteria range to include all three rows (e.g. H1:H3). Conditions on the same row are joined with AND instead.

Do DSUM and DCOUNT work in old versions of Excel?

Yes. The database functions have been part of Excel since the early versions and work in Excel 2016, 2019, 2021, and 365 identically — there's no #NAME? risk the way there is with newer functions like GROUPBY. They're one of the oldest, most stable corners of Excel.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-20.

Related guides: Excel DGET · Excel Database Functions · Excel SUMIFS · Excel COUNTIFS · Excel FILTER