Google Sheets DSUM Function: The Ultimate Guide

The DSUM function in Google Sheets is part of a whole suite of Google Sheets functions that work like SQL queries. These functions process data that is 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 are going to discuss the Google Sheets DSUM function, along with some examples and use-cases.

 

 

What Does the Google Sheets DSUM Function Do?

The DSUM function is used to find the sum of numbers in a column (of a database-like range) that satisfy a given criteria. In this way, it is a lot like the SUMIFS function.

 

How is DSUM 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 properly 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 is specified within the function parameters. So if you have a lot of conditions, you usually end up getting a complex formula with a number of 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 the form of 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:

=DSUM(database,field, criteria)

Here,

  • database is the range of cells that you want to apply DSUM to. The database should have a header row that contains the field names for each column.
  • field is the column within the database that you want to sum. This parameter can either be the field name of the column (eg: “Sales”) or a number depicting the index of the column in the database.
  • 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”}

If you’re finding it hard to follow so far, the examples in the next few sections are going to make things clearer.

For each of these examples, we will use the following database:

 google sheets dsum

 

How to Use DSUM with Single Criterion

Let us first take the simplest example. We want to find the sum of all sales made by the manufacturing department.

Before we actually 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’, we can have a small table on the side with the header or field name, ‘Department’ and the criterion below it, that says ‘Manufacturing’, as shown below:

 dsum function

Now we can use this table in the DSUM function 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:

 how to use dsum

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:

 google sheets dsum

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:

 how to use dsum

 

Finding DSUM with Multiple Criteria in Different Columns

Now let us go a step further and specify multiple criteria for the sum. So this time, 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:

 google sheets dsum

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:

 dsum function

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 formula. You can also specify wildcards (*, ?, ~) in your criteria table.

 

Finding DSUM with > or < Criteria

Now let us look at a special case where we want to specify a 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:

dsum function

The DSUM formula will then be:

=DSUM(A1:F9,"Sales",H3:H4)
dsum function

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:

how to use dsum in excel

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:

 how to use dsum

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:

how to use dsum in excel

 

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 we want the values 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:

dsum function

Your DSUM formula would then be:

=DSUM(A1:F9,”Sales”,H3:I4)

And when you use this formula in DSUM function here’s what you get:

 dsum function

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, rather than creating a separate criteria table, you can include the criteria parameters as an array.

In an array, columns are always separated by commas and rows are separated by semicolons. So if you want to convert the following criteria table into an array in the condition parameter of DSUM, you would specify it as:

=DSUM(A1:F9,"Sales",{"Department","Location";"Manufacturing","New York"})
google sheets dsum

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:

 how to use dsum in excel

Notice this gives the same result as the one which used the same criteria table.

 

The Bottom Line

In this tutorial we covered the Google Sheets DSUM 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.

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.