The DSUM function is used to add up or sum the values in a column of data that meet the set criteria.
These instructions apply to Excel 2019, 2016, 2013, 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Microsoft 365, and Excel Online.
DSUM Function Overview
The DSUM function is one of Excel'sdatabase 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 requiredargumentsare:
- 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 tutorialusesto find the amount of sap collected as listed in theProductioncolumn 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 tolook only at data for black and silver maple trees,enter the tree names under theMaple Treefield name.
To find data for more than one tree, enter each tree name in a separate row.
In cell A14, type the criteria, Black.
In cell A15, type the criteria Silver.
In cell D16, type the heading Gallons of Sap to indicate the information the DSUM function delivers.
Naming the Database
Using anamed rangefor expansive ranges of data such as a database can not only make it easier to enter anargument into the function, but it can also prevent errors caused by selecting the wrong range.
Named ranges areuseful if you use the same range of cells frequently in calculations or when creating charts or graphs.
Highlight cells A2 to E11 in the worksheet to select the range.
Click on the name box above column A in the worksheet.
Type Trees into the name box to create the named range.
Press the Enter key on the keyboard to complete the entry.
Using the DSUM Dialog Box
A function'sdialog boxprovides 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 theformula barabove the worksheet.
The final function looks like this:
=DSUM(Trees,"Production",A13:E15)
Excel Online does not feature function dialog boxes. You must enter the function manually when using Excel Online.
Click on cell E16, which is the location where the results of the function will be displayed.
Click on the Insert Function (fx) button to the left of the formula bar to bring up the Insert Function dialog box.
Type DSUM in the Search for a function box at the top of the dialog box.
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.
Click OK to open the DSUM function dialog box. Once the DSUM dialog box is open, you can complete the arguments.
Click on the Database line of the dialog box.
Type the range name Trees into the line.
Click on the Field line of the dialog box.
Type the field name "Production" into the line. Be sure to include the quotation marks.
Click on the Criteria line of the dialog box.
Drag select cells A13 to E15 in the worksheet to enter the range.
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, youcould use the regularSUM function, since you do not need to specify criteria to limit which data the function include.
Was this page helpful?
Thanks for letting us know!
Get the Latest Tech News Delivered Every Day
Subscribe
Tell us why!