Excel DSUM Function Tutorial

Learn how to sum selected records only with the DSUM function

The DSUM function is one of Excel's database functions. Excel database functions assist you when working with an Excel database. A database typically takes the form of a large table of data, where each row in the table stores an individual record. Each column in the spreadsheet table stores a different field or type of information for each record.

Database functions perform basic operations, such as count, max, and min, but they enable the user to specify criteria, so that the operation is performed on selected records only. Other records in the database are ignored.

01
of 02

DSUM Function Overview and Syntax

The DSUM function is used to add up or sum the values in a column of data that meet the set criteria.

DSUM Syntax and Arguments

The syntax for the DSUM function is:

``= DSUM (database, field, criteria)``

The three required arguments are:

• Database specifies the range of cell references containing the database. The field names must be included in the range.
• Field indicates which column or field is to be used by the function in its calculations. Enter the argument either by typing the field name in quotes, such as Radius, or entering the column number, such as 3.
• Criteria lists the range of cells containing the conditions specified by the user. The range must include at least one field name from the database and at least one other cell reference indicating the condition to be evaluated by the function.
02
of 02

Using Excel's DSUM Function Tutorial

Refer to the image accompanying this article as you work through the tutorial.

This tutorial uses to find the amount of sap collected as listed in the Production column of the example image. The criteria used to filter the data in this example is the type of maple tree.

To find the amount of sap collected only from black and silver maples:

1. Enter the data table as seen in the example image into cells A1 to E11 of a blank Excel worksheet.
2. Copy the field names in cells A2 to E2.
3. Paste the field names in cells A13 to E13. These are used as part of the Criteria argument.

Selecting the Criteria

To get DSUM to look only at data for black and silver maple trees, enter the tree names under the Maple Tree field name.

To find data for more than one tree, enter each tree name in a separate row.

1. In cell A14, type the criteria, Black.
2. In cell A15, type the criteria Silver.
3. In cell D16, type the heading Gallons of Sap to indicate the information the DSUM function delivers.

Naming the Database

Using a named range for large ranges of data such as a database can not only make it easier to enter an argument into the function, but it can also prevent errors caused by selecting the wrong range.

Named ranges are useful if you use the same range of cells frequently in calculations or when creating charts or graphs.

1. Highlight cells A2 to E11 in the worksheet to select the range.
2. Click on the name box above column A in the worksheet.
3. Type Trees into the name box to create the named range.
4. Press the Enter key on the keyboard to complete the entry.

Opening the DSUM Dialog Box

A function's dialog box provides an easy method for entering data for each of the function's arguments.

Opening the dialog box for the database group of functions is done by clicking on the Function Wizard button (fx) located next to the formula bar above the worksheet.

1. Click on cell E16—the location where the results of the function will be displayed.
2. Click on the Function Wizard icon to bring up the Insert Function dialog box.
3. Type DSUM in the Search for a function window at the top of the dialog box.
4. Click on the GO button to search for the function.
5. The dialog box should find DSUM and list it in the Select a function window.
6. Click OK to open the DSUM function dialog box.

Completing the Arguments

1. Click on the Database line of the dialog box.
2. Type the range name Trees into the line.
3. Click on the Field line of the dialog box.
4. Type the field name "Production" into the line. Be sure to include the quotation marks.
5. Click on the Criteria line of the dialog box.
6. Drag select cells A13 to E15 in the worksheet to enter the range.
7. Click OK to close the DSUM function dialog box and complete the function.
8. The answer 152, which indicates the number of gallons of sap collected from black and silver maple trees, should appear in cell E16.
9. When you click on cell C7, the complete function
= DSUM (Trees, "Production", A13:E15) appears in the formula bar above the worksheet.

To find the amount of sap collected for all trees, you could use the regular SUM function, since you do not need to specify criteria to limit which data is used by the function.

Database Function Error

The #Value error occurs most often when the field names are not included in the database argument. For this example, be sure that the field names in cells A2:E2 are included in the named range Trees.

Format
mla apa chicago