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.
What is DSUM?
The DSUM function returns the sum of values selected based on criteria applied to a field in your table-like array. 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.
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. Use 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 Field & 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. DSUM Result
Remember to close the parenthesis and press ‘Enter’ to see the result.
Examples of DSUM in Google Sheets
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).
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREGoogle 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 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.
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet
READ MOREGoogle 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.
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.
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.
You now know about the DSUM function and its syntax, as well as how to set up the criteria arrays. You know how to use DSUM to sum the values based on a single criterion. You also know how to sum values based on multiple criteria applied to a single column using OR logic and using AND logic. Finally, you know how to use DSUM to sum values based on both AND and OR logic, applied to different columns.
To learn about other methods of adding numbers in Google Sheets, check out our guide on How to Add Numbers in Google Sheets.