Excel DSUM Function Tutorial

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

DSUM Function Overview

The DSUM function is one of Excel's database functions. 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 only looks at selected records, and ignores other records in the database.

DSUM Syntax and Arguments

The syntax for the DSUM function is:

The three required arguments are:

• Database specifies the range of cell references containing the database. You must include the field names 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.
• The Criteria argument is 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.

Using Excel's DSUM Function 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, enter the data table as seen in the example image into cells A1 to E11 of a blank Excel worksheet. Then, copy the field names in cells A2 to E2, and paste them in cells A13 to E13. The field names in A13 to E13 will be 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 expansive 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.

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

The final function looks like this:

=DSUM(Trees,"Production",A13:E15)

1. Click on cell E16, which is the location where the results of the function will be displayed.

2. Click on the Insert Function (fx) button to the left of the formula bar to bring up the Insert Function dialog box.

3. Type DSUM in the Search for a function box at the top of the dialog box.

4. Click on the Go button to search for the function. The dialog box should find DSUM and list it in the Select a Function window.

5. Click OK to open the DSUM function dialog box. Once the DSUM dialog box is open, you can complete the arguments.

6. Click on the Database line of the dialog box.

7. Type the range name Trees into the line.

8. Click on the Field line of the dialog box.

9. Type the field name "Production" into the line. Be sure to include the quotation marks.

10. Click on the Criteria line of the dialog box.

11. Drag select cells A13 to E15 in the worksheet to enter the range.

12. Click OK to close the DSUM function dialog box and complete the function.

The result is 152, which indicates the number of gallons of sap collected from black and silver maple trees, and should appear in cell E16.

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 the function include.