By using the DSUM function, you can specify criteria and conditions regarding which cells should be added together. An alternative to using DSUM is using SUMIF, but SUMIF is not suitable for complex criteria.
The list below shows monthly and daily product sales. In cell G5 we have calculated a running total using the DSUM function, which takes into account a number of criteria that has been set up in the range A1:D2
We will use the DSUM function to calculate the total sales that meets the following criteria: Monthly sales for February that are greater than $500 and where the weekday is Tuesday and the product is Ipoh Coffee.
You can download the workbook to practice here.
The syntax for the DSUM function is: = DSUM(database, field, criteria)
The arguments of the DSUM function are explained below.
Database:
Is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field:
Indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as “Age” or “Yield,” or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria:
Is the range of cells that contains the conditions you specify. You 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 for specifying a condition for the column.
Applies To: MS Excel 2003,2007,2010 and 2013
1. Select the cell G5.
2. Enter the formula below:
=DSUM(A5:D19,4,A1:D2)
(A5:D19) is the database, (4) is the field number for sales, (A1:D2) is the
criteria range.
3. Press Enter.
4. The answer will be $2,400.00.
