How to Use Excel DSUM Function (4 Appropriate Examples)

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.

DSUM function overview-Excel DSUM Function

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)

DSUM syntax-Excel DSUM Function

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

Used as a function

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.

Used as a function result

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.


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.

Single criterion sum-Excel DSUM Function

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.

Single criterion sum result

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.


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.

Multiple criteria sum

Press ENTER. The aggregate value appears.

Multiple criteria sum-Excel DSUM Function

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.


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.

VBA Macro-Excel DSUM Function

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

 

Macro Code

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.

Result using vba macro code-Excel DSUM Function


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.

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo