The DSUM Google Sheets function is part of a whole suite of Google Sheets functions that work like SQL queries. These functions process data in table-like arrays or ranges with proper headers (like in a database).
The names of these database functions are usually prefixed by the letter ‘D’. For example, DSUM, DGET, and DPRODUCT are just some of the functions in the suit of database functions.
In this tutorial, we will discuss how to use DSUM in Google Sheets, along with some examples and use cases.
Table of Contents
What Does the DSUM Function in Google Sheets Do?
The DSUM function is used to find the sum of values selected in a column (of a database-like range) that satisfy given criteria. In this way, it is a lot like the SUMIFS function.
How is the DSUM Google Sheets Function Different from SUMIFS?
Both DSUM and SUMIFS primarily find the sum of items, given a set of conditions. However, there are some basic differences between the two.
- DSUM requires your data table to be structured and adequately organized with proper column headers or ‘field labels’. SUMIFS, on the other hand, can work with any set of data, including columns that are spread out in different parts of the worksheet.
- In SUMIFS, the criteria are specified within the function parameters. So if you have a lot of conditions, you usually get a complex formula with many parameters that make it hard to read, understand, and correct. DSUM, on the other hand, allows you to specify the criteria to be written externally in a separate table, with field labels matching the database’s field labels. As such, it keeps your formula clean and short and your conditions organized, easy to understand, and easy to correct.
Note: DSUM also allows you to specify the criteria within the formula in an array. We will see how to specify criteria through an array in the last section of this tutorial.
Syntax for the Google Sheets DSUM Function
The syntax for all database functions is the same. Here’s a look at the syntax for the DSUM function in Google Sheets:
=DSUM(database,field, criteria)
Here,
- database is the range of cells that you want to apply DSUM to. The database table should have a header row that contains the field names for each column.
- field is the column you want to sum within the database. This parameter can either be the field name of the column (e.g., “Sales”) or a number depicting the index of the column in the database table.
- criteria is the range of cells containing the criteria for summing the values. These criteria specify the records (or rows) that will be included in the calculation. The criteria should include at least one column label and at least one cell below the column label containing the condition for a column.
As mentioned before, the criteria can also be specified as an array created by an array expression. For example, {“Department”;” Manufacturing”}
Google Sheets DSUM Examples
If you’re finding it hard to follow so far, the examples in the following few sections are going to make things more straightforward.
For each of these examples, we will use the following database:
You can access this database here.
How to Use DSUM Function in Google Sheets with Single Criterion
Let us first take the simplest DSUM example Google Sheets. We want to find the sum of all sales made by the manufacturing department.
Before we enter the formula, we need to create a separate table that will specify the condition (or criterion). Since we want to sum only those Sales values where Department is ‘Manufacturing’,
Step 1: Create a small table on the side with the header or field name, ‘Department’ and the criterion below it, that says ‘Manufacturing’, as shown below:
Step 2: Type in = DSUM(
Step 3: Select the entire database
Step 4: Add a comma, then type Sales and enclose it in quotation marks
Step 5: Add a comma and select the criteria table you had created in step 1
Step 6: Close the brackets and press Enter
The Google Sheet DSUM function is as follows:
=DSUM(A1:F9,”Sales”,H3:H4)
Note: The first parameter should include the entire range of cells that make up the database, including the column headers (or field names).
Here’s the result we get when the above formula is applied to our sample database:
If you want total sales for more than one departments, say “Manufacturing” as well as “Operations”, then you can include each criterion in a separate cell under the column under “Department”, as shown below:
Your formula will then be:
=DSUM(A1:F9,”Sales”,H3:H5)
Here’s the result we get when this formula is applied to the sample database:
Finding DSUM with Multiple Criteria in Different Columns
Now let us go a step further and specify Google Sheets DSUM multiple criteria for the sum. So this time in our DSUM Google Sheets example, let us find the total sales made by the manufacturing department in the city of New York.
This means we now have two conditions:
- Department= “Manufacturing”
- Location= “New York”
For this, we first need to create the table for the criteria as follows:
Notice we used the two fields (“Department” and “Location”) in two separate criteria columns, with their corresponding conditions specified in their respective columns.
Next, use this criteria table in the DSUM function as follows:
=DSUM(A1:F9,”Sales”,H3:I4)
Here’s the result we get when this formula is applied to the sample database:
You can similarly include as many conditions as you need to by simply adding a new column to the criteria table and including its range in the DSUM sheets formula. You can also specify wildcards (*, ?, ~) in your criteria table.
Using DSUM Criterion Inside the Formula
You can also use the DSUM criterion inside the formula instead of a criteria range. For instance, in our example above for multiple criteria, we have the formula =DSUM(A1:F9,”Sales”,H3:I4)
The H3:I4 are the cell references for the criteria department and location. This can be replaced with {{“Department”;”Manufacturing”},{“Location”;”Newyork”}}
The new formula will then be:
=DSUM(A1:F9,"Sales",{{"Department";"Manufacturing"},{"Location";"Newyork"}})
This will return the same results as the previous formula.
Finding DSUM with > or < Criteria
Now let us look at a special case where we specify criteria as less than or more than a value. For example, what if we want to find the sum of all sales made by all employees who joined after 01/01/2020?
In this case, we can specify the condition as “>01/01/2020” under the field “Joining Date”. So let us first construct the criteria table as follows:
The DSUM formula will then be:
=DSUM(A1:F9,"Sales",H3:H4)
Similarly, if you want the sum to include sales made by employees who joined before 12/31/2019, your criterion table would look like this:
Finally, you can also include an OR condition in your criteria table. So if you want to include sales of employees who joined after 01/01/2020 or before (say) 06/01/2019, this is how your criteria table would look:
Your DSUM formula would then be:
=DSUM(A1:F9,”Sales”,H3:H5)
Here’s the result we get when this formula is applied to the sample database:
Finding DSUM with < and > Criteria
Now let us take a look at a case where we want to include sales made by employees who joined between the dates 06/01/2019 and 01/01/2020. This means the values to be extracted are after 06/01/2019 and before 01/01/2020.
When you want to include an AND condition like this, you can specify them as two separate conditions in two separate columns of the criteria table, as follows:
Your DSUM formula would then be:
=DSUM(A1:F9,”Sales”,H3:I4)
And when you use this formula in the DSUM function in Google Sheets here’s what you get:
Note: You can use other comparison operators (like <>,<=,>=) as well, if required, in the criteria table.
Finding DSUM Using an Array Expression
Finally, if you prefer to specify your conditions in your formula itself, you can include the criteria parameters as an array rather than creating a separate criteria table.
In an array, columns are always separated by commas, and semicolons separate rows. So if you want to convert the following criteria table into an array in the condition parameter of the DSUM function in Google Sheets, you would specify it as:
=DSUM(A1:F9,"Sales",{"Department","Location";"Manufacturing","New York"})
In the above formula, we specified all the criteria fields first, separated by commas, since they should all be in the same row of the criteria table. After that, we specified the next row by a delimiter semicolon “;”, followed by the actual criteria for each field separated by commas.
Note that the criteria should be in the same order as their respective criteria fields.
When the above formula is applied to our sample dataset, here’s what you get:
Notice this gives the same result as the one which used the same criteria table.
Potential Issues With the Dsum Formula in Google Sheets (Unwarranted Cases)
Sometimes the Google Sheets DSUM function will give a zero result instead of the correct calculation. This can happen if you try to SUM non-numerical values in the field part of the syntax. So in our above examples, we were able to use “sales” as the field but using “location” or “department” would give a zero as the result. If you need to calculate with non-numerical figures, you should use SUMIF or SUMIFS instead.
A second situation where you could run into a similar error is when the labels don’t match between your criteria table and data set, so be sure to check for any spelling errors or missing words from either..
Frequently Asked Questions
Is DSUM better than SUMIF?
The DSUM function in Google Sheets is quite similar to SUMIF since both of them will return the same results. However, unlike SUMIF, the DSUM function in Google Sheets works with structured data in a database like the query function. In general, this would make SUMIF better than DSUM since you don’t need to organize your data when using SUMIF.
However, if you have a lot of conditions, SUMIF will give a complex formula with many parameters that make it hard to read, understand, and correct.
DSUM, on the other hand, allows you to specify the criteria to be written externally in a separate table, with field labels matching the database’s field labels. As such, it keeps your formula clean and short, and your conditions organized, easy to understand, and correct. This makes DSUM better when you have multiple conditions.
Can DSUM Include Multiple Criteria?
Yes, the DSUM function in Google Sheets is able to include multiple criteria. In this case we would use multiple fields) in separate criteria columns, with their corresponding conditions specified in their respective columns.
The Bottom Line
In this tutorial we covered the DSUM Google Sheets function and explained how to use it with different kinds of conditions. The DSUM function in Google Sheets is a powerful and convenient function that really helps you work with your Google Sheets data, as if it were a database.
We hope our explanations have encouraged you to try your hand at database functions like DSUM, DPRODUCT, etc., and see for yourself how convenient they can be.
For more tips on data analysis tips, check out our article on Quick and Easy Data Analysis with Google Sheets.
