Home > Formulas > Functions > Database

Excel DSUM Function Examples

Debra Dalgleish - Contextures

These examples show how you can summarize data, based on one or more criteria, using Excel database functions, like DSUM, DGET, or DCOUNT.

Excel database function examples

Excel Database Functions

There are 12 Excel database functions that you can use to summarize data, based on one or more criteria. I show how to use these functions, in the examples below.

Function

Description

DAVERAGE Returns the average of selected database entries
DCOUNT Counts the cells that contain numbers in a database
DCOUNTA  Counts nonblank cells in a database
DGET  Extracts from a database a single record that matches the specified criteria
DMAX  Returns the maximum value from selected database entries
DMIN  Returns the minimum value from selected database entries
DPRODUCT  Multiplies the values in a particular field of records that match the criteria in a database
DSTDEV  Estimates the standard deviation based on a sample of selected database entries
DSTDEVP  Calculates the standard deviation based on the entire population of selected database entries
DSUM  Adds the numbers in the field column of records in the database that match the criteria
DVAR  Estimates variance based on a sample from selected database entries
DVARP  Calculates variance based on the entire population of selected database entries

Database Function Syntax

All 12 of the database functions have the same 3 arguments in their syntax:

  1. database - where the data is stored
  2. field - which column in the database to use
  3. criteria - which items to summarize from the data

For example, this DSUM formula adds up the units sold, in an orders table, based on criteria entered in cells E1:F2:

  • =DSUM(dbOrders,”Units”,E1:F2)

DSUM formula adds up the units sold

Database Function Tips

Single Cell: Because DSUM uses a criteria range, it's a good choice for a single cell summary, at the top of a worksheet, or in a dashboard.

  • Note: Don't use database functions for multiple row summaries in a list. Instead, use other functions, like SUMIFS or COUNTIFS.

All Versions: The database functions work in all versions of Excel, so they're a good choice if you're sharing files with people who use much older versions of Excel.

Criteria Setup: You can set up a simple criteria range, or something complex. If you're familiar with Excel's Advanced Filter setup, it's just like that! I show a few criteria examples in the sections below.

Sum Amounts With DSUM

If you need to get a one-cell total in Excel, based on criteria, you can use the DSUM database function.

In this example, I show how to calculate the total number of units sold, from a table with sales data, based on specific criteria.

Set Up Workbook for DSUM

To use the DSUM function, you need two ranges in the workbook:

  • A database range
  • A criteria range

Database Range: Sales Order Data

In this example, the database contains sales order details, and is in a table named tblOrders.

DSUM function

  • In the workbook, I created a named range, dbOrders, which is based on the tblOrders data and headings.
  • I'll use the dbOrders name in my DSUM formula

DSUM function

Criteria Range Setup

Next, you need to set up a criteria range.

The criteria range must include the following cells:

  • one row of cells, for heading cells
  • one or more rows with criteria rules, directly below the header row
  • one or more columns

Rep and Item Criteria

For this example, the criteria range is shown in the screenshot below:

  • The criteria range is in 2 columns, and 2 rows - E1:F2
  • Column headings exactly match headings in the database - Rep and Item
  • In row 2, I've typed a sales rep’s name, and an item that was sold.

DSUM function

Create the DSUM Formula

Next, I'll create a DSUM formula, with the 3 required arguments:

  • =DSUM(database, field, criteria)

Here is the completed formula in cell B4:

  • =DSUM(dbOrders,”Units”,E1:F2)

The result is 299 - the number of units sold by Jones, for the item, Pen.

DSUM function

How the Formula Works

The DSUM formula has 3 arguments:

  • =DSUM(database, field, criteria)
  • =DSUM(dbOrders, "Units", E1:F2)
1) Database

The sales data is in a named range,dbOrders, which is based on tblOrders table.

2) Field

The formula should sum the numbers in the field (column) named "Units". This is a text value, so it's enclose indouble quote marks

3) Criteria

The criteria range is worksheet cells E1:F2. In the screenshot below, the Rep name is Jones, and the Item name is Pen.

The formula result is the total quantity of pen orders sold by Jones.

DSUM function

Create Exact Criteria

In the example shown above, the DSUM function treats the text criteria as “begins with”, rather than “equal to”.

So, the total units sold would include any pen, pen sets, and pencils that were sold by Jones, because those items all begin with “Pen”

If you want to find only the items that are equal to “Pen”:

  • In cell F2, change the criterion to this formula:
    • ="=Pen"

With this criterion in cell F2, the total units is 91, which only includes the Pen sales, not pencils, or pen sets.

DSUM function

  • Tip: For more information and examples for setting up the criteria range, please see criteria range setup section on the Advanced Filter page – it uses the same type of setup.

Add More Criteria Rows

You’re not just limited to one row in the criteria area. For example, you could add more rep names and items, and expand the Criteria Range in the DSUM formula.

In the example shown below, the criteria range is now E1:F4

  • =DSUM(dbOrders,”Units”,E1:F4)

DSUM calculates the total units for orders where:

  • Jones is the rep, AND the item name begins with Pen
    • OR
  • Gill is the rep, AND the item name begins with Binders
    • OR
  • Gill is the rep, AND the item name begins with Pen

DSUM function

Excel DGET Example

The DGET function has the same 3 arguments as the other database functions. However, it has one key difference - it returns a value from a single record, instead of a summary from all the matching records.

In the screen shot below, this formula is in cell B12:

  • =DGET(Database,"Date",B6:C7)

In this workbook, the data is in a named range, Database, and the field is "Date".

  • There is only one record with Kivell as the rep, and a weekday of Sun.
  • The sale date is 1/14/2024

DGET function returns value from a single record

DGET Errors

  • If there are multiple matching records, the DGET function returns a #NUM! error
  • If there are no matching records, the DGET function returns a #VALUE! error

For example, in the screen shot below, I deleted the rep name in cell B7.

  • Now the criteria row means "any Rep", and weekday Sun.
  • There are several records that match those criteria
  • The DGET result is a #NUM! error

DGET function returns value from a single record

More Database Functions

In the Database Functions workbook, the Ex01 sheet uses multiple rep names, and multiple weekdays for the criteria.

  • The criteria range uses COUNTIF formula, to check for records that contain one of the criteria names AND one of the selected weekday names.
    • CheckRep: =COUNTIF($B$6:$B$8,Orders!D2)
    • CheckDay: =COUNTIF($D$6:$D$7,Orders!B2)
  • In the results area, there are 5 database function formulas:
    • DSUM, DCOUNT, DAVERAGE, DMAX, and DMIN.
  • All the formulas are exactly the same, except for the function name:
    • =Dfunction(Database,"Units",rngCrit)

DGET function returns value from a single record

Get the Excel Files ✅

  • DSUM Tables: Download the completed DSUM workbook for the example from this page. The zipped file is in xlsx format, and does not contain any macros.
  • Database Functions: Download the Database Functions workbook which has examples of a few functions, including DSUM, and DGET. The zipped file is in xlsx format, and does not contain any macros
  • DSUM Function: This workbook has an older example of the DSUM and DCOUNT functions. The zipped file is in xls format, and does not contain any macros.
  • Criteria Examples: Get the Excel workbook with Advanced Filter criteria examples. The zipped workbook is in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

🐰 Down the Rabbit Hole 🐰

In this section, you'll find notes on using Excel database functions. These things are nice to know, but not essential, so you can skip this section, and get back to work. Or dive in, for some extra Excel fun and knowledge!

  • 1) Define a Named Range
  • 2) Criteria Range Notes
  • 3) Criteria Range Notes
  • 4) Criteria Videos
  • 5) Named Table Video
  • 6) Formulas in Criteria Cells
  • 1) Define a Named Range

    For the DSUM example, I created a named range for the sales data table, by following the steps below:

    • Go to the Orders sheet, where the named table is located, and click on
    • On the Excel Ribbon, click the Formulas tab, and click Define Name.
    • In the New Name dialog box, type a name for the range – dbOrders
    • Next, press the Tab key twice, to move to the Refers To box
    • Press the Delete key, to delete any existing information
    • Then, on the worksheet, point to the upper left corner of the orders table
    • When the pointer changes to an angled black arrow, click once, to select the table data. The table name will appear in the Refers To box.
      • =tblOrders
    • Click again, to include the heading cells in the selection.
    • The Refers box now shows a structured table reference
      • =tblOrders[#All]
    • Click the OK button, to complete the name.

    DSUM function

    2) Use Structured Table Reference

    If you don't want to create a named range based on the order table, you can use a structured table reference in the formula.

    • A structured reference might include the table name, with a column [Units] or item [#All] specifier, inside square brackets

    To add the reference, click twice, in the top left corner of the table, to select the data (first click) and the headings (second click).

    • =DSUM(tblOrders[#All],

    DSUM function

    3) Criteria Range Notes

    The criteria range for a database function can be as simple as one heading cell, with a single ciriterion in the cell below. Or, you can create large, complex criteria ranges, with multiple rows and column, to create AND / OR conditions.

    DSUM function

    Criteria Examples: To learn more about database function criteria setup, go to my Criteria Examples page. The examples there are for Advanced Filters, but the setup rules and sample formulas will work exactly the same for Excel database functions. Or, click here to get my Excel workbook with these Advanced Filter criteria examples.

    4) Criteria Example Videos

    In this video, I show how to set up an AND / OR criteria range, starting at the 1:45 mark.

    In this video, I show another criteria setup, with formulas in the second row.

    5) Named Table Video

    In this video, I show how to create a named table in Excel.

    6) Use Formulas in Criteria Cells

    If you want to use more than a few criteria rows, it can be confusing and cumbersome to create a large criteria range. Instead, you can use formulas in the criteria cells, similar to the criteria formulas that you can use for an Advanced Filter.

    • Tip: If you’re using formulas in the criteria range:
      • leave the heading cell blank
      • OR, use a heading that is NOT used in the database.

    In the example shown below, the criteria headings have been changed to RepCount and ItemCount.

    • At the right are two named tables – tblRepSel and tblItemSel
    • In those tables, I've entered the Rep names, and the Item names, that I want to include in the DSUM total.

    DSUM function

    Enter the Criteria Formulas

    Next, in cells E2 and F2, we’ll use COUNTIF formulas, to check if:

    • the rep name from the database is in the tblRepSel table,
    • the item name from the database is in the tblItemSel table.

    The COUNTIF function syntax has 2 arguments:

    • range - range of cells to check
    • criteria - what value to look for

    In the criteria formula, for the range argument, it's okay to use a named table reference

    However, for the criteria argument, you have to use a normal cell reference, to a cell in the first row of data in the database, using a relative reference

    For example, here's the formula in cell E2. It refers to the Rep column in the orders table, and cell D2 on the Orders sheet:

    • =COUNTIF(tblRepSel[Rep],Orders!D2)

    The formula in F2 refers to the Item column in the orders table, and cell E2 on the Orders sheet:

    • =COUNTIF(tblItemSel[Item],Orders!E2)

    DSUM function

    The criteria cells show the results for the first row in the database, and that row has “Jones” and “Pencil”.

    • The DSUM function will calculate for ALL the rows in the database, because the formula has a relative reference for the criteria argument

    Check the DSUM Formula

    The DSUM formula is the same as in the previous example:
    =DSUM(dbOrders,”Units”,E1:F2)

    DSUM function

    The DSUM result is 377, and we can check that by filtering the database to show the same items.

    Then, select the Units column in the table, and check the Sum calculation in the Status Bar.

    • Tip: You can click on any calculation in the Status Bar, to copy the number. Then, go to a blank cell, and paste (Ctrl + V), to compare the Status Bar amount with a formula result

    DSUM function

    If we had used a table reference in our COUNTIF formulas, for the Criteria argument, the DSUM would have been incorrect – it adds up all the rows!

    DSUM function

    More Tutorials

    Sum Functions

    Count Functions

    Functions List

    Last updated: March 26, 2025 8:14 PM