|
Home > Formulas > Functions > Database Excel DSUM Function Examples
|

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 |
All 12 of the database functions have the same 3 arguments in their syntax:
For example, this DSUM formula adds up the units sold, in an orders table, based on criteria entered in cells E1:F2:

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.
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.
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.
To use the DSUM function, you need two ranges in the workbook:
In this example, the database contains sales order details, and is in a table named tblOrders.


Next, you need to set up a criteria range.
The criteria range must include the following cells:
For this example, the criteria range is shown in the screenshot below:

Next, I'll create a DSUM formula, with the 3 required arguments:
Here is the completed formula in cell B4:
The result is 299 - the number of units sold by Jones, for the item, Pen.

The DSUM formula has 3 arguments:
The sales data is in a named range,dbOrders, which is based on tblOrders table.
The formula should sum the numbers in the field (column) named "Units". This is a text value, so it's enclose indouble quote marks
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.

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”:
With this criterion in cell F2, the total units is 91, which only includes the Pen sales, not pencils, or pen sets.

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 calculates the total units for orders where:

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:
In this workbook, the data is in a named range, Database, and the field is "Date".

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

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

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!
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!
For the DSUM example, I created a named range for the sales data table, by following the steps below:

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.
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).

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.

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.
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.
In this video, I show how to create a named table in Excel.
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.
In the example shown below, the criteria headings have been changed to RepCount and ItemCount.

Next, in cells E2 and F2, we’ll use COUNTIF formulas, to check if:
The COUNTIF function syntax has 2 arguments:
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:
The formula in F2 refers to the Item column in the orders table, and cell E2 on the Orders sheet:

The criteria cells show the results for the first row in the database, and that row has “Jones” and “Pencil”.
The DSUM formula is the same as in the previous example:
=DSUM(dbOrders,”Units”,E1:F2)

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.


Last updated: March 26, 2025 8:14 PM