Part 1: Introduction to DSUM Function in Microsoft Excel
Definition
The DSUM function in Microsoft Excel is a database function that adds the numbers in a column of a list or database based on given conditions.
Purpose
The DSUM function is used when you want to sum specific rows that satisfy the given conditions in a database.
Syntax & Arguments
The syntax for the DSUM function is:
=DSUM(database, field, criteria) Explanation of Arguments
Database: This is the range of cells that makes up the database. The database should include a header row.
Field: Indicates which column of the database you want to sum. It can either be a number indicating the position of the column in the database (1 for the first column, 2 for the second, and so on) or the name of the column in quotation marks (like “Amount”).
Criteria: This is the range of cells that contains the conditions you want to apply. The requirements include at least one column header and one cell below the header specifying the column’s condition.
Return Value
The DSUM function will return the sum of the selected database entries based on the specified conditions.
Remarks
The DSUM function will not sum text, logical values, or empty cells in the field of cells you are adding.
Part 2: Examples of DSUM Function in Microsoft Excel
Example 1: Basic Use of DSUM Function
Purpose of Example
This example demonstrates the DSUM function’s primary use to calculate a column’s sum based on a specific condition.
Data Tables and Formulas
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | |
| 2 | Food | 100 | |
| 3 | Transport | 150 | |
| 4 | Food | 200 | =DSUM(A1:B4, "Amount", A1:B1) |
Result Table
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | Sum |
| 2 | Food | 100 | |
| 3 | Transport | 150 | |
| 4 | Food | 200 | 450 |
Explanation
In this example, the DSUM function calculates the sum of the “Amount” column for all rows. The result, displayed in cell C4, is 450, the sum of all the amounts.
Example 2: DSUM with Text Field Argument
Purpose of Example
This example demonstrates the use of the DSUM function with a text field argument.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Food | |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | =DSUM(A1:B4, "Amount", A1:C2) |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Food | Sum |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | 300 |
Explanation
In this example, the DSUM function calculates the sum of the “Amount” column, but only for rows where the “Category” column is equal to “Food”. The result in cell D4 is 300, the sum of the “Food” category amounts.
Example 3: DSUM with Multiple Criteria
Purpose of Example
This example demonstrates the use of the DSUM function with multiple criteria.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Cost | Food | 150 |
| 2 | Food | 100 | 50 | ||
| 3 | Transport | 150 | 100 | ||
| 4 | Food | 200 | 150 | =DSUM(A1:C4, "Amount", A1:D2) |
Result Table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Cost | Food | Sum |
| 2 | Food | 100 | 50 | ||
| 3 | Transport | 150 | 100 | ||
| 4 | Food | 200 | 150 | 300 |
Explanation
In this example, the DSUM function calculates the sum of the “Amount” column, but only for rows where the “Category” column is equal to “Food” and the “Cost” column is less than or equal to 150. The result, displayed in cell E4, is 300, the sum of the “Food” category amounts with a “Cost” of less than or equal to 150.
Example 4: DSUM with COUNT Function
Purpose of Example
This example demonstrates how to use the DSUM function with the COUNT function to calculate the total of a particular data set and count the number of specific items in a column.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Count | |
| 2 | Food | 100 | 1 | |
| 3 | Transport | 150 | 2 | |
| 4 | Food | 200 | 3 | =DSUM(A1:B4, "Amount", A1:B1) * COUNTIF(A2:A4, "Food") |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Count | Result |
| 2 | Food | 100 | 1 | |
| 3 | Transport | 150 | 2 | |
| 4 | Food | 200 | 3 | 600 |
Explanation
In this example, the DSUM function calculates the sum of the “Amount” column for all rows. The COUNTIF function counts the number of “Food” items in the “Category” column. The result is the product of the sum calculated and the count of “Food” items, which is 600.
Example 5: DSUM with MAX Function
Purpose of Example
This example demonstrates how to use the DSUM function with the MAX function to calculate the maximum sum amount across multiple categories.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 200 | |||
| 4 | Food | 150 | =MAX(DSUM(A1:B4, "Amount", A1:C2), DSUM(A1:B4, "Amount", A1:D2)) |
Result Table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | Result |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 200 | |||
| 4 | Food | 150 | 350 |
Explanation
In this example, the DSUM function is used twice to calculate the sum of the “Amount” column for “Food” and “Drink” categories separately. The MAX function is then used to determine the maximum sum amount between the two types. The result is 350, which is the maximum sum amount.
Example 6: DSUM with MIN Function
Purpose of Example
This example demonstrates how to use the DSUM function with the MIN function to calculate the minimum sum amount across multiple categories.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 200 | |||
| 4 | Food | 150 | =MIN(DSUM(A1:B4, "Amount", A1:C2), DSUM(A1:B4, "Amount", A1:D2)) |
Result Table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Food | Drink | Result |
| 2 | Food | 100 | 100 | 200 | |
| 3 | Drink | 200 | |||
| 4 | Food | 150 | 250 |
Explanation
In this example, the DSUM function is used twice to calculate the sum of the “Amount” column for “Food” and “Drink” categories separately. The MIN function is then used to determine the minimum sum amount between the two types. The result is 250, which is the minimum sum amount.
Example 7: DSUM with IF Function
Purpose of Example
This example demonstrates how to use the DSUM function in conjunction with the IF function to calculate the sum of a particular data set, conditional upon a specific criterion being met.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | ||
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | =DSUM(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, "")) |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Sum | |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | 300 |
Explanation
In this example, the DSUM function is used to calculate the sum of the “Amount” column, but only for rows where the “Category” column is equal to “Food”. The IF function is used to specify this condition, and the DSUM function then calculates the sum based on this condition. The result is 300, which is the sum of 100 and 200.
Example 8: DSUM with IFERROR Function
Purpose of Example
This example demonstrates how to use the DSUM function with the IFERROR function to handle potential errors when calculating sums based on certain conditions.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | ||
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | =IFERROR(DSUM(A1:B4, "Amount", IF(A2:A4="Travel", A1:B1, "")), "No Data") |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Sum | |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | No Data |
Explanation
In this example, the DSUM function is used to calculate the sum of the “Amount” column, but only for rows where the “Category” column is equal to “Travel”. However, since there are no rows with “Travel” in the “Category” column, this would typically result in an error. The IFERROR function catches this error and returns “No Data” instead.
Example 9: DSUM with ROUND Function
Purpose of Example
This example demonstrates how to use the DSUM function with the ROUND function to calculate the sum of a particular data set and round the result to a specified number of decimal places.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | ||
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | =ROUND(DSUM(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, "")), 1) |
Result Table
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Amount | Sum | |
| 2 | Food | 100 | ||
| 3 | Transport | 150 | ||
| 4 | Food | 200 | 300.0 |
Explanation
In this example, the DSUM function is used to calculate the sum of the “Amount” column, but only for rows where the “Category” column is equal to “Food”. The ROUND function then rounds this sum to one decimal place. The result is 300.0.
Example 10: DSUM with SUMPRODUCT Function
Purpose of Example
This example demonstrates how to use the DSUM function with the SUMPRODUCT function to calculate a weighted sum based on certain conditions.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Weight | ||
| 2 | Food | 100 | 0.5 | ||
| 3 | Transport | 150 | 0.3 | ||
| 4 | Food | 200 | 0.2 | =DSUM(A1:C4, "Amount", IF(A2:A4="Food", A1:B1, "")) * SUMPRODUCT(B2:B4, C2:C4) |
Result Table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Amount | Weight | Result | |
| 2 | Food | 100 | 0.5 | ||
| 3 | Transport | 150 | 0.3 | ||
| 4 | Food | 200 | 0.2 | 75 |
Explanation
In this example, the DSUM function is used to calculate the sum of the “Amount” column for rows where the “Category” column is equal to “Food”. The SUMPRODUCT function then calculates the sum of the products of the “Amount” and “Weight” columns. The final result is the product of these two values, representing a weighted sum of 75.
Part 3: Tips and Tricks
Criteria Layout: Make sure your criteria range has the same column headers as your database. This is how Excel knows what rows you refer to in your criteria.
Text Criteria: Text criteria can include wildcards: question marks (?) for single characters and asterisks (*) for any number of characters.
Logical Operators: You can use logical operators (>,<,>=,<=,<>) in your criteria. If you use these, remember to enclose the criteria in quotation marks.
Multiple Conditions: You can add them to your criteria range if you need to use various conditions. Remember, each new row in your criteria range is treated as an OR condition, and each new column as an AND condition.
Remember, practice makes perfect. The more you use the DSUM function, the more comfortable you’ll become with its capabilities and potential applications. Enjoy crunching numbers!