The Excel DSUM function returns the sum of values from a set of records that match criteria. The values to sum are extracted from a given field in the database.
Syntax: =DSUM (database, field, criteria)
The DSUM function syntax has the following arguments:
- Database (required argument) – This is the range of cells wherein the first row of the database specifies the field names.
- Field (required argument) – This is the column within the database that will be summed. Field can be a field name that is the header provided at the top row such as “Area,” “Sales,” etc. or it can be a number.
- Criteria (required argument) – This is a range of cells that contains the criteria specified by us. It specifies the record that will be included in the calculation. We can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which the condition for the column is specified.
Example: Let’s look at some Excel DSUM function examples and explore how to use the DSUM function as a worksheet function in Microsoft Excel:
Suppose we are given the data below:
We will use the DSUM function to calculate the total sales of doll in quarters 3 and 4. The criteria specified are:
- Quarter: >II
- Product: Doll
We get 1305 as the result.
Suppose we are given the sales data. We wish to find out sales made by all sales reps with names starting with the letter A. Here, we will use A* to find out the figure.
We are given the data below:
The criteria specified are:
- Quarter: >II
- Rep: *A
The DSUM function finds out all sales made after Quarter II by reps with names starting with the letter A. The result we get is 410.
In the example, instead of typing in “Sales” for the field argument, we simply used the number 4 (to denote the fourth column of the database).
- DSUM supports wildcards in criteria
- Criteria can include more than one row (as explained above)
- The field argument can be supplied as a name in double quotes (“”) or as a number representing field index.
- The database and criteria ranges must include matching headers.