Maruf Islam
Excel DSUM function is a DATABASE sum function. The DSUM function calculates the sum of specified fields following specified criteria. It takes three mandatory arguments: Range, Field, and Criteria.
In this article, you’ll get to learn how you can use the DSUM function with appropriate examples.
Download Excel Workbook
Excel DSUM Function: Syntax and Arguments
⦽ Function Objective:
The DSUM function calculates the total sum of a specific Field by matching specific Criteria from a given Range.
⦽ Syntax:
DSUM (database, field, criteria)
⦽ Arguments Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| range | Required | range of cells that hold all the entries |
| field | Required | Indicates the column to be calculated for sum |
| criteria | Required | range of cells where specific conditions are assigned |
⦽ What Can be Used as Criteria:
DSUM offers multiple criteria types to filter data from the range. Some most used criteria types are
| Criteria | Type | Output |
|---|---|---|
| “Unit Price” | String | Rows match “Unit Price” |
| Cook* | Wildcard | Rows start with “Cook” |
| *ies | Wildcard | Rows end with “ies” |
| 120 | Number | Equal to 120 |
| >120 | Comparison | Greater than 120 |
| <120 | Comparison | Less than 120 |
| >=120 | Comparison | Greater than or equal 120 |
| <>120 | Comparison | Not equal 120 |
| <> | Comparison | Not blank |
| =B7 | Formula | Equal to the argument of B7 |
⦽ Return Parameter:
The DSUM function returns a sum value.
⦽ Applies To:
Microsoft Excel version 2000 to Office 365, Excel version 2011 for Mac and onwards.
4 Suitable Examples to Use the Excel DSUM Function
Example 1: DSUM Used as a Function
Like all other functions, DSUM is an Excel function, and it works as such. You just have to declare the arguments as instructed by the syntax.
➧ Paste the following formula in any blank cell (i.e., G5:H5) to calculate the sum of the Unit Price field.
=DSUM(B8:H19,"Unit Price",B5:C6)Inside the formula,
B8:H19; is the range.
“Unit Price”; is the specified field of which you calculate the sum.
B5:C6; range where specific criteria exist.
➧ Press ENTER. Then the evaluated value will appear.
By the formula, we impose two criteria
⏩ Sum Unit Price of Order IDs greater than 10021.
⏩ Sum Unit Price of Quantity sold greater than or equal 120.
The DSUM function evaluates $3.74. It sums the favorable entries (i.e. $1.87 and $1.87) and results in ($1.87+$1.87) $3.74.
You can use different criteria depending on your data types and the DSUM function works just fine.
Read More: How to Use Database Functions in Excel (With Examples)
Example 2: DSUM Calculates Total Sum (Single Criterion)
Similar to the SUM function, the DSUM function can calculate the total sum of any Field (i.e., Any Column). In this case, we calculate the Total Price of every sold product from the dataset.
➧ Write the below formula in any cell (i.e., G5:H5).
=DSUM(B8:H19,"Total Price",B5:C6)In the formula,
B8:H19; indicates the range.
“Total Price”; indicates the specified field of which you calculate the sum.
B5:C6; refers to the range where specific criteria exist.
➧ Hit ENTER. Afterward, the total sum value will appear.
The formula imposes only one criterion
⏩ To sum the Total Price of Order IDs equal to or less than 10017 that means all the entries in the dataset.
The resultant value of the formula is $2033.01. It sums all the entries in the Total Price column. You can use other headers as fields to come up with the total sum.
Read More: Can Excel DGET Return Multiple Records [See 4 Solutions]
Example 3: DSUM Calculates Sum (Multiple Criteria)
From the prior example (i.e., Example 2), we learn the DSUM function works similarly to the SUM function. But what if we just want to sum a specific field that complies with multiple conditions?
In this scenario, we impose four criteria in a range (i.e., B5:E6) and DSUM sums entries of Total Price field which have
⏩ Order ID equal or greater than 10017.
⏩ Region East.
⏩ Positioned in Cookies category.
⏩ Identified as Arrow Root Product.
➧ Write the following formula in any cell (i.e., G5:H5).
=DSUM(B8:H19,"Total Price",B5:E6)The references declare the same arguments as they do in previous examples. All the criteria sit in the B8:H19 range as we can see.
The formula matches every specified field to criteria and moves rightward to finally match appropriate entries.
➧ Press ENTER. The aggregate value appears.
The formula finally matches 3 entries that comply with the imposed conditions and returns a value of $695.42.
If we cross-check the resultant value with matched entries, the value appears to be the same ($318.28+$303.02+$74.12) $695.42.
Read More: How to Use DCOUNT Function in Excel (5 Suitable Examples)
Example 4: DSUM Used in VBA Macros
We can also use the DSUM function in VBA Macro codes. Following the Macro DSUM function format, we can mimic any previous examples of this article.
Let’s say, we want the sum of the Total Price of every entry in the dataset.
➧ Hit ALT+F11 altogether. In a moment Microsoft Visual Basic Window opens up. In the Microsoft Visual Window, Select Insert > Choose Module.
➧ In the Module, Paste the following Maco code then Hit F5 to run the code.
Sub ExcelDSUMFunction()
Range("F5:G5").Value = Application.WorksheetFunction.DSum(Range("B8:H19"), "Total Price", Range("B5:C6"))
End Sub
In the Macro code,
“F5:G5”; indicates where the resultant value will sit.
➧ Back to the worksheet and you’ll see the sum of Total Price entries in cell F5:G5.
Differentiate SUMIF, SUMIFS and DSUM:
| Aspects | SUMIF | SUMIFS | DSUM |
|---|---|---|---|
| Syntax | SUMIF(range, criteria, [sum_range]) | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) |
DSUM(database, field, criteria)
|
| Database | Conditional Function | Conditional Function | A database Function |
| Formation | No Particular Formation is Needed | No Particular Formation is Needed | Requires Field Labels to Operate |
| Imposing Criteria | Single Criterion can be Inserted Inside or Outside the Formula | Multiple Criteria can be Inserted Inside or Outside the Formula and Look messy but Flexible. | Criteria are Defined Outside or Inside the Formula and Look Clean |
| Handling Multiple Criteria in Same Position |
Not Applicable |
Unable to Handle Multiple Criteria in Same Position | Handles with Ease |
| Understanding | Comparatively Easy to Understand than SUMIFS Function | Harder to Understand and Apply | Easily Understood |
| Building Complex Criteria | Custom Complex Criteria Building is Hard | Very East to Build Custom Complex Criteria | Hard to Build Custom Complex Criteria |
⧭ Things to Keep in Mind While Using DSUM
🔼 The criteria range can be anywhere in the worksheet. However, it is preferred not to place criteria range in positions like overlapping with the dataset, and below the dataset.
🔼 If DSUM has to perform to the whole dataset, place a blank line below the header of the criteria range.
🔼 Any range of criteria can be used if it consists of at least one column field and one condition.
Conclusion
I hope the above-described uses of the DSUM function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. You can check out my other articles on the Exceldemy website.
