DSUM vs SUMIFS: Database Functions or Modern Criteria

DSUM vs SUMIFS comes down to where the conditions live. SUMIFS keeps them inside the formula, while DSUM, one of Excel's older database functions, reads them from a small block of cells on the sheet. Both add up numbers from rows that match. This guide compares them on the same data, shows the one DSUM trap that catches almost everyone, and explains when the older function is still the better tool.

DSUM vs SUMIFS at a glance#

SUMIFS is easier for summary tables, and DSUM is easier for OR logic and unusual conditions.

Both ignore capital letters, so "north" and "North" match the same rows.

How DSUM works#

DSUM adds the numbers in one column of a table, using only the rows that match a criteria range. It takes three arguments:

=DSUM(database, field, criteria)

• database is the whole table, including the header row, such as A1:E9.
• field is the column to add: its header in quotes, like "Revenue", or its position, like 5.
• criteria is a block of cells with at least one header copied from the table and a condition below it.

The criteria headers must match the table headers exactly. Under each header you can type text, a number or a comparison such as >=4 under Units. To give one column two conditions, such as units from 2 to 5, repeat the header side by side: Units with >=2 and Units with <=5 on the same row. An empty cell under a header means "any value", so one criteria range can hold optional filters that users fill in or leave blank. That flexibility is what makes DSUM useful as a simple filter panel on a report sheet. In the criteria range, conditions on the same row must all be true (AND). Conditions on different rows are alternatives (OR). The same rules apply to the other database functions, such as DCOUNT, DAVERAGE, DMAX and DMIN, and to the Advanced Filter.

How SUMIFS works#

SUMIFS takes the range to add first, then pairs of "column to check" and "what it must match":

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Every pair must be true for a row to count. The data doesn't need headers, and the criteria can be text, numbers, operators like ">=4", wildcards or cell references. The SUMIFS function guide lists every argument, and SUMIF vs SUMIFS explains how it differs from its one-condition cousin.

The same totals solved both ways#

Here is a sales list in A1:E9. Note the Northeast row: it matters.

Task 1: North and Laptop (AND)#

With SUMIFS:

=SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop")

The result is 4,800 (rows 2 and 6).

With DSUM, type Region in G1, Product in H1, North in G2 and Laptop in H2. Then:

=DSUM(A1:E9,"Revenue",G1:H2)

The result is 9,600, not 4,800. DSUM reads plain text as "begins with", so North also matches Northeast. To match exactly, type ="=North" in G2. The cell then shows =North, and DSUM returns 4,800. In SUMIFS, the same "begins with" behaviour needs a wildcard: "North*", as in the exercise on this page.

Task 2: North or Laptop (OR across two columns)#

With DSUM, put the alternatives on separate rows. Use the headers in G1:H1, ="=North" in G2, leave H2 empty, leave G3 empty and type Laptop in H3:

=DSUM(A1:E9,"Revenue",G1:H3)

The result is 13,500. Each data row is counted once, even if it matches both criteria rows.

With SUMIFS, add the two totals and take away the overlap, or the North laptops are counted twice:

=SUMIFS(E2:E9,A2:A9,"North")+SUMIFS(E2:E9,C2:C9,"Laptop")-SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop")

That is 6,300 + 12,000 − 4,800 = 13,500. It works, but DSUM's version is easier to read and change.

Task 3: a condition written as a formula#

DSUM accepts computed criteria: a formula under a header that is not a column name. To add rows where revenue per unit is over 1,000, type High price in J1 and =E2/D2>1000 in J2, referring to the first data row:

=DSUM(A1:E9,"Revenue",J1:J2)

The result is 12,000 (rows 2, 5, 6 and 7). SUMIFS can't compare two columns, so the modern equivalent is SUMPRODUCT: =SUMPRODUCT((E2:E9/D2:D9>1000)*E2:E9) gives the same 12,000. See SUMPRODUCT vs SUMIFS for when that's the better choice.

When to use DSUM#

DSUM is worth knowing when the conditions themselves need to be visible and editable on the sheet.

• OR logic across different columns, like Task 2, where SUMIFS needs the add-and-subtract trick.
• A filter panel for non-technical users. People change the words in the criteria cells, and the total updates without anyone touching a formula.
• Conditions written as formulas, like Task 3.
• Files that already use DSUM or the Advanced Filter, since both share the same criteria range.

When to use SUMIFS#

SUMIFS is the better everyday choice, especially for reports.

• Summary tables. =SUMIFS($E$2:$E$9,$B$2:$B$9,G2) fills down a list of reps in one go: Mei's row gives 3,950. With DSUM, every rep needs a separate criteria range.
• Exact matches by default, so Northeast never sneaks into North.
• Conditions in single cells, joined with &: ">="&G2. The wildcards and cell-based criteria lesson practises this.
• Ranges and between values, such as units from 2 to 5. See SUMIFS between two numbers.
• Optional filters, where a blank cell means "any". See SUMIFS that ignores blank criteria.

Common mistakes with DSUM and SUMIFS#

These slips cause most wrong totals:

1. Forgetting the header row in DSUM. The database must start at the headers, A1:E9, not A2:E9. Otherwise the field name isn't found and DSUM returns #VALUE!.
2. Headers that don't match. "Revenue " with a trailing space in the criteria range is a different header.
3. A blank row in the DSUM criteria range. An empty criteria row matches every record, so DSUM adds everything. Make the criteria range end at the last filled row.
4. Plain text in DSUM criteria. Remember that North means "begins with North". Use ="=North" for an exact match.
5. Putting the sum range last in SUMIFS. It always comes first.
6. Double-counting OR with SUMIFS. When both conditions can be true for the same row, subtract the overlap, as in Task 2.

For the full multi-condition pattern with COUNTIFS too, see the COUNTIFS and SUMIFS guide.

Our recommendation#

Use SUMIFS for almost all conditional totals. It fills down cleanly, matches exactly, and it is the function most Excel tests and colleagues expect. Learn DSUM as a specialist tool for OR logic across columns, conditions written as formulas, or a criteria panel that other people edit. If you meet DSUM in an inherited file, check the criteria cells for the "begins with" trap before trusting the total.

In short: SUMIFS by default, DSUM when the conditions belong on the sheet.

Practise it#

Try the exercise on this page to see how a wildcard reproduces DSUM's "begins with" matching. Then practise SUMIFS with several conditions in a live spreadsheet: you type each formula and it checks it instantly.