The DSUM function with examples in Excel

The DSUM function in Excel is a powerful tool for calculating the sum of specified fields based on specific criteria. Let’s dive into how it works and explore some examples:

1. Syntax:

   • The DSUM function has the following syntax:

     DSUM(database, field, criteria)
     

   • Here’s what each argument means:
     • database: The range of cells that hold all the entries (your data).
     • field: Indicates the column (field) to be calculated for the sum.
     • criteria: The range of cells where specific conditions are assigned.

2. Criteria Types:

   • DSUM allows various criteria types to filter data from the range. Some commonly used criteria include:
     • "Unit Price": Rows matching the string “Unit Price.”
     • Cook*: Rows starting with “Cook” (wildcard).
     • *ies: Rows ending with “ies” (wildcard).
     • 120: Rows equal to the number 120.
     • >120: Rows greater than 120.
     • <120: Rows less than 120.
     • >=120: Rows greater than or equal to 120.
     • <>120: Rows not equal to 120.
     • =B7: Rows equal to the value in cell B7 (formula).

3. Example 1: Summing Unit Price:

   • Suppose you want to calculate the sum of the “Unit Price” field. Use this formula:

     =DSUM(B8:H19, "Unit Price", B5:C6)
     

     • In this formula:
       • B8:H19 is the range.
       • "Unit Price" is the specified field.
       • B5:C6 contains specific criteria (e.g., Order IDs greater than 10021 or Quantity sold ≥ 120).
     • The DSUM function evaluates to $3.74 by summing favorable entries ($1.87 + $1.87).

4. Example 2: Total Price Calculation:

   • Let’s calculate the total price of every sold product from the dataset:

     =DSUM(B8:H19, "Total Price", B5:C6)
     

     • Here, we sum the “Total Price” field based on the same criteria.
     • The result will be the total sum value.

Remember, DSUM is a versatile function that adapts to different criteria, making it useful for various scenarios in Excel!