=DSUM (database, field, criteria)
The Excel DSUM function is designed to return a sum value for a group of values that match certain criteria. The values to be summed up are gotten from a particular field in the overall database.
Purpose of the Excel DSUM Function
Used to get the sum of values matching a given criteria
Generated Value for the Excel DSUM Function
The sum calculated for the values that meet the criteria
Logical Parameters used in the Excel DSUM Function
- Database: This is the range of database, headers inclusive.
- Field: This is the index to count or the field name.
- Criteria: This is the range covered by the criteria, headers inclusive of the Criteria range including headers.
Figure 1: Example of how to use the Excel DSUM Function to get the sum of phone sales at 2 different dates
The formula used is:
(B8:D13,"Sales",B5:D6) (using field by their name)
(B8:D13,3,B4:D5) (using field by their index)
This gives the sum of all “Phone” sales between 12/10/2018 – 15/10/2018
Notes of Usage for the Excel DSUM Function
- The Excel DSUM function is used to calculate the sum for values within a set of records that are found to match the given criteria. The values that will be summed are gotten from a given field in the overall database and this is specified as the argument.
- The argument used in the database is a cell range which includes the field header.
- Field refers to the name or the field index value that will be queried.
- Criteria are the cell range including headers that match everything within the argument.
- Index or name can be used to refer to a field.
- Criteria Alternatives that can also be used in the Excel DSUM Function
- Various expressions can be used to express the criteria, including wildcards. Some examples are shown in the table below:
Note about criteria alternative when using the Excel DSUM Function
The Excel DSUM function doesn’t have an extensive support for some wildcards as with other functions such as SUMIFS, COUNTIFS, MATCH etc. For instance, the pattern ???? will match strings with 4 exactly texts in more advanced functions, but this cannot be used in database function. Where wildcards are used, make sure to test carefully.
Multiple Row Criteria applied in the Excel DSUM Function
The range of criteria for the Excel DSUM function includes more than a row below the header. For more than a row, each of this row is merged with the ‘OR’ logic, while the various expressions in a given criteria row are normally merged using the ‘AND’ logic.
General Note about the Excel DSUM Function
- The Excel DSUM function is fashioned to supports wildcards as criteria
- Criteria to be used can be more than a single row (already explained above)
- Field argument can be given as a name using double quotes (e.g., “sales”) or as a number (e.g., 4) which is a representation of the field index.
- Always make sure that the criteria and database range always include the exact matching headers.