How to Use Excel DSUM Function: 4 Methods

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.

Used as a function

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.

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.

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.

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.


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.

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.


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.

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


Excel DSUM Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo