Summing Records With DSUM

DSUM adds up the numbers in one column of a table, but only for the rows that match a criteria range. Think of it as a SUMIFS that reads its conditions from a block of cells instead of from inside the formula.

It shines when you have many conditions or want users to change the filters by editing cells rather than rewriting formulas.

The pattern is:

DSUM(database, field, criteria)

• database - the full table including headers, e.g. A1:C13.
• field - the column to total, given as a header name in quotes like "Amount" or as a column number.
• criteria - the criteria range you built earlier.

Imagine sales data in A1:C13 with headers Region, Rep, Amount. In E1 put Region and in E2 put East.

The formula below totals the Amount column for every East row. If East rows are 1200, 800, and 500, the result is 2500. Change E2 to West and the total instantly recalculates - no formula edit needed.

The field argument decides which column gets summed. Two ways to write it:

• By name: "Amount" - clear and resistant to column reordering.
• By position: 3 - the third column of the database range.

Using the header name is usually safer because inserting a column will not break it. Just keep the spelling identical to the table header.

You can total based on a numeric rule. Put Amount in E1 and >1000 in E2. Now DSUM adds only the amounts above 1000.

This is handy for questions like 'what is the total of all large orders?' The comparison lives in a cell, so a manager can adjust the threshold without touching the formula.

To total East orders over 1000, build a two-column criteria range. Put headers Region and Amount in E1:F1, then East and >1000 in E2:F2.

Because both conditions sit on the same row, they are joined with AND. DSUM sums Amount only for rows that are East and above 1000.

To total both East and West, stack the values on separate rows. Put Region in E1, East in E2, and West in E3.

Separate rows mean OR, so DSUM adds the Amount for any row that is East or West. Remember to extend the criteria argument to E1:E3 so it covers both condition rows.

Both can sum with conditions, but they differ:

• SUMIFS keeps criteria inside the formula - great for one-off totals.
• DSUM reads criteria from cells - great for dashboards where users tweak filters, or for complex AND/OR logic that SUMIFS handles awkwardly.

If you find yourself nesting many SUMIFS for OR logic, DSUM with a multi-row criteria range is often cleaner.

The database argument must include the header row. If you pass only the data rows (A2:C13) without headers, DSUM cannot map the field name to a column and returns an error.

Also keep the range tight around your real data. Including stray blank rows below is fine for sums but can confuse other D-functions, so it is a good habit either way.

If no rows match the criteria, DSUM simply returns 0 - it does not throw an error. That is usually fine, but a zero can look like a real total.

Pair it with DCOUNT to know whether any records actually matched, or wrap the result with a check so a zero from no matches reads differently from a zero total.

The real payoff of DSUM is interactivity. Place a dropdown in the criteria value cell E2 listing each region, then point DSUM at E1:E2.

Now a single cell drives the total: pick East and the figure shows East sales; pick North and it flips to North. You can stack several DSUM cells - one per metric - all reading the same dropdown, turning a flat table into a responsive summary panel with zero formula edits.

You wrote =DSUM(A1:C13, "Amount", E1:E2) with Region in E1 and East in E2. What does it return?

DSUM totals one column for rows matching a criteria range:

• Syntax: DSUM(database, field, criteria).
• Include the header row in the database argument.
• The field can be a quoted header name or a column number.
• Same-row criteria = AND; separate-row criteria = OR.
• No matches returns 0, not an error.

Next we count matching records with DCOUNT.