Method 1 – Using DSUM 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 in which you calculate the sum.
B5:C6; range where specific criteria exist.
➧ Press ENTER. 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 to 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.
Depending on your data types, you can use different criteria, and the DSUM function works just fine.
Method 2 – Applying DSUM to Calculate the Total Sum in Excel (Single Criterion)
Similar to the SUM function, the DSUM function can calculate the total sum of any Field (i.e., Any Column). 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 in 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.
Method 3 – Using DSUM Function in Excel to Calculate 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 up 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 the Total Price field which have
⏩ Order ID equal to or greater than 10017.
⏩ Region East.
⏩ Positioned in the 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 match appropriate entries finally.
➧ 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.
Method 4 – Applying DSUM Function in Excel VBA Macros
Use the DSUM function in VBA Macro codes. Following the Macro DSUM function format, we can mimic any previous article examples.
LWant the sum of the Total Price of every entry in the dataset.
➧ Hit ALT+F11 altogether. 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 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 the Same Position | Not Applicable | Unable to Handle Multiple Criteria in the Same Position | Handles with Ease |
| Understanding | Comparatively Easier 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 |
⧭ DSUM Function in Excel: Things to Keep in Mind
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.
Download Excel Workbook
Uses of Excel DSUM Function.xlsm
Excel DSUM Function: Knowledge Hub
- How to Use DSUM Function with Dynamic Criteria in Excel
- Excel DSUM vs SUMIF Functions
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!