# DSUM Function in Excel

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

syntax
`=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

ABC
1CategoryAmount
2Food100
3Transport150
4Food200`=DSUM(A1:B4, "Amount", A1:B1)`

Result Table

ABC
1CategoryAmountSum
2Food100
3Transport150
4Food200450

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

ABCD
1CategoryAmountFood
2Food100
3Transport150
4Food200`=DSUM(A1:B4, "Amount", A1:C2)`

Result Table

ABCD
1CategoryAmountFoodSum
2Food100
3Transport150
4Food200300

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

ABCDE
1CategoryAmountCostFood150
2Food10050
3Transport150100
4Food200150`=DSUM(A1:C4, "Amount", A1:D2)`

Result Table

ABCDE
1CategoryAmountCostFoodSum
2Food10050
3Transport150100
4Food200150300

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

ABCD
1CategoryAmountCount
2Food1001
3Transport1502
4Food2003`=DSUM(A1:B4, "Amount", A1:B1) * COUNTIF(A2:A4, "Food")`

Result Table

ABCD
1CategoryAmountCountResult
2Food1001
3Transport1502
4Food2003600

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

ABCDE
1CategoryAmountFoodDrink
2Food100100200
3Drink200
4Food150`=MAX(DSUM(A1:B4, "Amount", A1:C2), DSUM(A1:B4, "Amount", A1:D2))`

Result Table

ABCDE
1CategoryAmountFoodDrinkResult
2Food100100200
3Drink200
4Food150350

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

ABCDE
1CategoryAmountFoodDrink
2Food100100200
3Drink200
4Food150`=MIN(DSUM(A1:B4, "Amount", A1:C2), DSUM(A1:B4, "Amount", A1:D2))`

Result Table

ABCDE
1CategoryAmountFoodDrinkResult
2Food100100200
3Drink200
4Food150250

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

ABCD
1CategoryAmount
2Food100
3Transport150
4Food200`=DSUM(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, ""))`

Result Table

ABCD
1CategoryAmountSum
2Food100
3Transport150
4Food200300

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

ABCD
1CategoryAmount
2Food100
3Transport150
4Food200`=IFERROR(DSUM(A1:B4, "Amount", IF(A2:A4="Travel", A1:B1, "")), "No Data")`

Result Table

ABCD
1CategoryAmountSum
2Food100
3Transport150
4Food200No 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

ABCD
1CategoryAmount
2Food100
3Transport150
4Food200`=ROUND(DSUM(A1:B4, "Amount", IF(A2:A4="Food", A1:B1, "")), 1)`

Result Table

ABCD
1CategoryAmountSum
2Food100
3Transport150
4Food200300.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

ABCDE
1CategoryAmountWeight
2Food1000.5
3Transport1500.3
4Food2000.2`=DSUM(A1:C4, "Amount", IF(A2:A4="Food", A1:B1, "")) * SUMPRODUCT(B2:B4, C2:C4)`

Result Table

ABCDE
1CategoryAmountWeightResult
2Food1000.5
3Transport1500.3
4Food2000.275

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

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

2. Text Criteria: Text criteria can include wildcards: question marks (?) for single characters and asterisks (*) for any number of characters.

3. Logical Operators: You can use logical operators (>,<,>=,<=,<>) in your criteria. If you use these, remember to enclose the criteria in quotation marks.

4. 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!