Google Sheets features many categories of built-in functions, including twelve database functions. These functions can only be used with data structured like a database table. In other words, the first row contains the fields or column labels, and the subsequent rows are the individual records. All the database functions have the same syntax, but each performs a different operation. When you need to sum values in your database based on specific criteria, you can use the DSUM function in Google Sheets.
In this guide, you will learn about the syntax for the DSUM function and how to format the criteria. First, you have step-by-step instructions on how to use DSUM with a single criterion. Additionally, you have detailed examples of how to use DSUM with multiple criteria applied to a single field and how to use it with multiple criteria applied to different fields. Finally, you will learn the difference between DSUM and the SUMIF and SUMIFS functions and when it’s appropriate to use each one. You’ll also see how to write criteria as an array literal and why DSUM sometimes returns 0.
What is DSUM?
The DSUM function returns the sum of values selected based on criteria applied to a field in your table-like array. The D stands for database, so DSUM works much like a sum query on a database table. Like other database functions in Google Sheets – e.g., DGET, DPRODUCT, or DCOUNT – DSUM requires data formatted like a database table, where the first row contains the field names.
DSUM syntax
The DSUM function in Google Sheets has the following syntax:
=DSUM(database, field, criteria)
- database: the array or range containing the data to consider. The data has to be structured as a table where the first row contains the column headers or field labels.
- field: specifies the column containing the values you want to sum. You can use the text corresponding to the column header or an index number. When using an index, remember that the value of the first column is “1”.
- criteria: an array or range with zero or more criteria to filter the values before summing them.
Criteria can be specified in two different ways:
- A range of cells containing the criteria array.
- An array expression describing the criteria array.
This guide will focus on the first method as it provides more flexibility: it’s easier to modify the criteria array. Additionally, the first method will save you a lot of typing and curly brackets. The array form is covered in the criteria section below.
How to use DSUM in Google Sheets
In the cell where you want the result, type the DSUM function and select your table as the database. Next, type the column label or field name as the field. Alternatively, you can use the column index. Finally, add the conditions that values must meet to be summed.
1. Set up criteria
The first step is to set up the criteria array. In this case, I will use a single criterion to sum the sales of FirstProduct. In an empty cell, type the field name (product) and type the criterion in the cell below it (FirstProduct).
2. Type the DSUM function
In the cell where you want the result, type the DSUM function. Select the range of cells containing the database or type the range name as the first parameter.
3. Add the field and criteria
For the second parameter, type the field label or column between quotation marks: “amount”. For the third parameter, select the range of cells containing your criterion.
4. Get the DSUM result
Remember to close the parenthesis and press ‘Enter’ to see the result.
How to write DSUM criteria in Google Sheets
The criteria array works like a small table. Its first row holds field names that match the headers of your database exactly, and the rows below hold the conditions. DSUM reads it with two rules:
- Conditions in the same row must all be true (AND).
- Conditions in different rows are alternatives (OR): a record counts if it matches any one row.
A condition can be a value, such as FirstProduct, or a comparison, such as >=1/1/22 or <2/1/22. To set a range on one field, repeat its name in two columns, as in the date example below.
DSUM with multiple criteria
Put each AND condition in its own column and each OR alternative in its own row. When you combine both, repeat the AND conditions on every row. A blank cell sets no condition, so a row that leaves one out matches every value in that field. The examples below walk through each case.
DSUM criteria as an array literal
Instead of a range, you can type the criteria into the formula as an array literal. Commas separate columns and semicolons separate rows: the first row holds the field names and each row after it holds conditions. With the sample data in this guide, this formula sums the sales of FirstProduct:
=DSUM(transactions,"amount",{"product";"FirstProduct"})
This one sums the sales of FirstProduct or SecondProduct made by Agent1:
=DSUM(transactions,"amount",{"product","sales_agent";"FirstProduct","Agent1";"SecondProduct","Agent1"})
If your spreadsheet locale uses a comma as the decimal separator, separate columns with a backslash instead of a comma. Array literals suit one or two fixed conditions. For longer criteria, a range is easier to read and change.
DSUM examples with multiple criteria
Now that you know how to use DSUM with a single criterion, it’s time to use it with multiple criteria. The first examples focus on multiple criteria applied to a single field. In the first case, I want to include rows that match any of the criteria (OR). In the second, I want to include rows with values in a specific range by specifying the maximum and minimum values (AND). The third example focuses on applying multiple criteria to different fields (AND).
Google Sheets DSUM with multiple criteria for the same column (OR)
Using DSUM, you can apply multiple criteria to the same column. Your criteria array can contain multiple rows under the field name, with each criterion on its own row. For this example, I want to sum the records if they contain “FirstProduct” or “SecondProduct”.
1. Set up the criteria array as shown below.
2. In an empty cell, type the DSUM function and select the table as the database.
3. Type the field between quotation marks, and after the comma, select the cells with the criteria array.
4. Press ‘Enter’ to see the result.
Google Sheets DSUM with multiple criteria for the same column (AND)
In this example, you’ll learn how to apply multiple criteria to a single column using AND. Follow the steps below to sum the sales made between two dates: on or between January 1st and January 31st.
1. Set up the criteria array as shown below.
2. In the cell where you want the result, type the DSUM function and select the table as the database.
3. Type the field between quotation marks as the second parameter, and select the cells with the criteria array as the third.
4. Close the parenthesis and press ‘Enter’ to see the result.
Google Sheets DSUM with multiple criteria for different columns
In this example, you will learn to apply different criteria to different fields by structuring the array using AND and OR logic. For records to be included in the sum, they must match all the criteria specified for each field.
In this case, the record must contain “FirstProduct” OR “SecondProduct” in the “product” field and “Agent1” in the “sales_agent” field. Additionally, the date must be on or after January 1st but before July 1st.
1. Set up the criteria array as shown below. In this case, it will contain four columns and three rows, as shown below. Since there are two conditions in the first field (OR), the criteria for the other fields must be repeated in each row.
2. In the cell where you want the result, type the DSUM function and select the table as the database.
3. Type the field name in quotes as the second parameter and select the criteria array as the third.
4. Press ‘Enter’ to see the result.
Why DSUM returns 0
If DSUM returns 0 when you expect a total, check the field first. Like SUM, DSUM adds only numbers, so pointing it at a text column, such as product names, gives you 0.
Then check the criteria labels. Each one has to match a database header exactly. A criteria label that says Protein won’t match a header that says Protein (g), so no record matches and the sum is 0.
What is the difference between DSUM and SUMIF?
SUMIF and SUMIFS can be used on data that is not formally structured. To use the DSUM function, you must structure your data like a database table, with field names or column headers in the first row. This makes it easy to specify criteria without writing long or complex expressions.
Therefore, if the data you want to use is not structured in a database-like table, you can use SUMIF or SUMIFS. However, if your data is already structured as a database table, DSUM requires less explicit cell referencing, and criteria can also be supplied in tabular form. If you prefer the formula approach, see how to use SUMIFS with multiple criteria.
Conclusion
The DSUM function in Google Sheets is useful and flexible. While it does require that you structure your data in a specific way, this makes it easier to use with multiple conditions using both AND and OR logic. The SUMIF and SUMIFS functions can be used with unstructured data, but you need to write longer conditions with many explicit cell references and logical operators to join them. DSUM, on the other hand, allows you to specify your criteria array as a table, where the placement of the field names and values determines the logic. In other words, if your data is already structured like a table in a database, DSUM will get results with a cleaner formula and much less typing.
To learn about other methods of adding numbers in Google Sheets, check out our guide on how to add numbers in Google Sheets.
DSUM is one of several ways to filter and total data in Google Sheets. For more control over which rows you pull out, see the FILTER formula and the QUERY formula.
English 
Español 
Português do Brasil 
Français