The `DSUM` function in Excel is used to sum values in a field (column) of a database that meet specific criteria. It is part of Excel’s database functions. Here’s a step-by-step guide on how to use the `DSUM` function:
Syntax
DSUM(database, field, criteria)
- database: The range of cells that makes up the database. The first row of the database should contain the field (column) names.
- field: Indicates the column that contains the values to sum. This can be either the column label in quotation marks (e.g., “Sales”) or a number that represents the column’s position in the database (e.g., 3 for the third column).
- criteria: The range of cells that contains the conditions you specify. It should include at least one column label and the criteria below it.
Example
Suppose you have a database in Excel that records sales data with columns labeled “Date”, “Salesperson”, “Region”, and “Amount”. You want to sum the “Amount” for a specified region.
| Date | Salesperson | Region | Amount |
|------------|-------------|----------|--------|
| 01-Jan-23 | John Doe | East | 100 |
| 02-Jan-23 | Jane Smith | West | 150 |
| 03-Jan-23 | John Doe | East | 200 |
| ... | ... | ... | ... |
| Region |
|----------|
| East |
=DSUM(A1:D10, "Amount", F1:F2)
- Set up the database: Assume your database range is A1:D10.
- Define the criteria range: Let’s say you want to sum the Amount for the “East” region.
- Place your criteria range somewhere in the sheet, e.g., F1:F2.
- Use the DSUM function:
- Position your formula where you want the result, e.g., in cell G1.
- Enter the DSUM formula:
- This formula sums all amounts in the “Amount” column for rows where the “Region” is “East”.
Important Points
- Criteria range: You can use a more complex criteria that includes multiple rows or columns. For example, to filter by both “Salesperson” and “Region”, you can expand your criteria range.
- Flexible criteria: The criteria can include text, numbers, expressions, or a combination of these. Use operators like `=`, `>`, `<`, `>=`, `<=`, and `<>` for conditions.
- Field argument: Use the exact column label (as shown in the table) or a column number. If you use the label, it must be in quotes.
- Dynamic ranges: If your data expands or changes often, consider using named ranges or dynamic references.
By understanding these concepts, you can effectively use `DSUM` to perform conditional sums across your datasets in Excel.
Unlock Your Potential
Excel
Access
Power BI
