DSUM
The DSUM function returns the sum of the values that are contained in a database and meet the given criteria.
This function is useful for calculating data arranged in a database format when you need to flexibly filter records before performing an operation.
Syntax
DSUM(database, field, criteria)
| Argument | Description | Permitted values |
|---|---|---|
| database | The range of data, the sum of which needs to be calculated. The first row of this range must contain column headers | Any range of cells |
| field | A column with values to sum of | Column header as text in quotation marks (“Price”) or a reference to the cell with the header, or the column number (1 for the first one) |
| criteria | Cell range containing data selection criteria | Cell range. Must consist of two rows: the first row contains the headers of the required columns, and the second row contains the values |
Examples of use
Initial data:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Quantity | Price |
| 2 | Apples | 10 | 50 |
| 3 | Pears | 5 | 60 |
| 4 | Apples | 15 | 50 |
| 5 | Oranges | 7 | 70 |
| ** ** | E |
|---|---|
| 1 | Item |
| 2 | Apples |
To calculate the total number of apples, use the following formula:
=DSUM(A1:C5, 2, E1:E2)
Result: 25 (10+15).
If you want to sum up the number of fruits with a price above 55, use the following set of criteria:
| ** ** | E |
|---|---|
| 1 | Price |
| 2 | >55 |
The formula will look like: =DSUM(A1:C5, 2, E1:E2)
Result: 12 (5+7).
Notes
- Make sure that the data range and criteria have the same headers so that the function can filter the data correctly.
- The DSUM function ignores empty cells and rows with text in numeric fields.