- Learn how to sum selected records using only the DSUM function
- DSUM function overview and syntax
- DSUM syntax and arguments
- How to Use the DSUM Functions Tutorial in Excel
- Choice of criteria
- Database name
- Opening the DSUM dialog
- Argument Completion
- Database function error
Learn how to sum selected records using only the DSUM function
The DSUM function is used to summarize or sum the values in a column of data that meet specified criteria.
Remark . These instructions apply to Excel 2016, 2013, 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Office 365, and Excel Online.
DSUM function overview and syntax
The DSOM function is one of the Excel database functions. The Excel database functions help you to work with the Excel database. A database is usually in the form of a large table of data, with each row in the table storing a separate record. Each column of a spreadsheet table contains a separate field or type of information for each record.
The database functions perform basic operations such as count, max, and min, but they allow the user to set criteria so that the operation is performed only on selected records. Other entries in the database are ignored.
DSUM syntax and arguments
The syntax for the DSOM function is:
= DSUM (база данных, поле, критерии)
Three required arguments:
- Database s defines a series of cell references that contain the database. Field names must be included in the scope.
- Field specifies which column or field will be used by the function in its calculations. Enter the argument by typing the field name in quotes, for example Ray or enter a column number, such as 3.
- criteria enumerate a range of cells containing user-specified conditions. The range must contain at least one field name from the database and at least one other cell reference that specifies the condition being evaluated by the function.
How to Use the DSUM Functions Tutorial in Excel
Refer to the image accompanying this article while working on the tutorial
This guide is used to determine the amount of juice harvested, as indicated in the “Production” column of the sample image. The criterion used to filter the data in this example is the maple tree type.
To find the amount of sap collected from black and silver maples only:
Enter the data table as shown in the sample image in the cells from A1 to E11 blank excel spreadsheet.
Copy field names in cells A2 to E2 .
Insert field names in cells A13 to E13 . They are used as part of an argument criteria .
Choice of criteria
To see DSUM data for black and silver maples only, enter the tree names below the field name Maple .
To find data for more than one tree, enter each tree name on a separate line.
In a cell A14 enter criteria black .
In a cell A15 enter criteria Silver .
In a cell D16 enter title liters of juice to specify the information provided by the DSUM function.
Using a named range for large data ranges, such as a database, can not only make it easier to pass an argument to a function, but also avoid errors caused by incorrect range selection.
Named ranges are useful if you often use the same range of cells in calculations or when creating charts or graphs.
Select cells A2 to E11 on the worksheet to select a range.
Press name field above column A on the worksheet.
Enter trees in the name field to create a named range.
Press key Enter on the keyboard to complete your entry.
Opening the DSUM dialog
The function’s dialog box provides an easy way to enter data for each of the function’s arguments.
The dialog box for a group of database functions is opened by pressing the “Function Wizard” (fx) button next to the formula bar above the worksheet.
There are no function dialogs in Excel Online. You must enter the function manually when using Excel Online.
Click on a cell E16 which will display the results of the function.
Press the button Insert function to open the Insert Function dialog box.
Enter DSUM in the function search box at the top of the dialog box.
Press the button TO GO to find the function. The dialog should find DSUM and list it in the function selection box.
Click Okay to open the DSOM Function dialog box.
When the DSUM dialog is open, you can complete the arguments.
Click on a line Database dialog box.
Enter a name for the range trees to a line.
Click on a line Field dialog box.
Enter field name “ Production” to a line. Make sure to put quotes.
Click on a line criteria dialog box.
Drag selected cells A13 to E15 on the sheet to enter a range.
Click Okay to close the DSOM Function dialog box and end the function.
Answer 152 indicating the gallons of sap harvested from black and silver maples should be displayed in cell E16.
Click on a cell C7 full function
=DSUM(trees, “Production”, A13:E15) displayed in the formula bar above the worksheet.
To find the amount of sap harvested for all trees, you can use the normal SUM function, because you don’t have to specify criteria to limit what data the function uses.
Type in Excel Online =DSUM(trees, “Production”, A13:E15) in a cell.
Database function error
Wrong #Where the usually occurs when field names are not included in the database argument. In this example, make sure the field names in cells A2:E2 are included in the named range trees .