Don’t forget to share this post

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).

DSUM in Google Sheets Function Examples Criteria Array
DSUM in Google Sheets: Function & Examples - Criteria Array

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.

DSUM in Google Sheets Function Examples DSUM Function
DSUM in Google Sheets: Function & Examples - DSUM Function

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.

DSUM in Google Sheets Function Examples Field Criteria
DSUM in Google Sheets: Function & Examples - Field & Criteria

4. DSUM Result

Remember to close the parenthesis and press ‘Enter’ to see the result.

DSUM in Google Sheets Function Examples DSUM Result
DSUM in Google Sheets: Function & Examples - DSUM 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).

How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

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. 1. Set up the criteria array as shown below.
DSUM in Google Sheets Function Examples Criteria Array OR
DSUM in Google Sheets: Function & Examples - Criteria Array OR
  1. 2. In an empty cell, type the DSUM function and select the table as the database.
DSUM in Google Sheets Function Examples Function Database
DSUM in Google Sheets: Function & Examples - Function & Database
  1. 3. Type the field between quotation marks, and after the comma, select the cells with the criteria array.
DSUM in Google Sheets Function Examples Select Criteria Array
DSUM in Google Sheets: Function & Examples - Select Criteria Array
  1. 4. Press ‘Enter’ to see the result.
DSUM in Google Sheets Function Examples DSUM Results OR
DSUM in Google Sheets: Function & Examples - DSUM Results OR

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. 1. Set up the criteria array as shown below.
DSUM in Google Sheets Function Examples Criteria Array AND
DSUM in Google Sheets: Function & Examples - Criteria Array AND
  1. 2. In the cell where you want the result, type the DSUM function and select the table as the database.
DSUM in Google Sheets Function Examples Function Database 2
DSUM in Google Sheets: Function & Examples - Function & Database
  1. 3. Type the field between quotation marks as the second parameter, and select the cells with the criteria array as the third.
DSUM in Google Sheets Function Examples Field Criteria Array
DSUM in Google Sheets: Function & Examples - Field & Criteria Array
  1. 4. Close the parenthesis and press ‘Enter’ to see the result.
DSUM in Google Sheets Function Examples DSUM Results AND
DSUM in Google Sheets: Function & Examples - DSUM Results AND
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

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 MORE

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. 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.
DSUM in Google Sheets Function Examples Criteria Array AND OR
DSUM in Google Sheets: Function & Examples - Criteria Array AND & OR
  1. 2. In the cell where you want the result, type the DSUM function and select the table as the database.
DSUM in Google Sheets Function Examples Function Database 3
DSUM in Google Sheets: Function & Examples - Function & Database
  1. 3. Type the field name in quotes as the second parameter and select the criteria array as the third.
DSUM in Google Sheets Function Examples Field Criteria Array 2
DSUM in Google Sheets: Function & Examples - Field & Criteria Array
  1. 4. Press ‘Enter’ to see the result.
DSUM in Google Sheets Function Examples DSUM Results AND OR
DSUM in Google Sheets: Function & Examples - DSUM Results AND & OR

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.

Maria Del Olmo
Originally published Mar 7 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets