If you’re looking to level up your Google Sheets skills, understanding how to use the DSUM function is a must. In this article, I’ll guide you through using this powerful function step by step, complete with supporting visuals to make your learning experience even more enjoyable.
What is the DSUM Function?
As the name suggests, the DSUM function allows you to obtain the sum of values in any numeric field within a database, based on specified criteria. Similar to the SUMIF/SUMIFS function, DSUM can yield similar results. However, in my opinion, DSUM offers a more user-friendly approach.
One notable distinction between DSUM and SUMIF/SUMIFS is that the former is a database function, returning the sum of values selected from a database table or a similar array/range using an SQL-like query. On the other hand, the latter is of a logical type.
If you’re eager to explore the differences in greater detail, you can refer to my tutorial on the Difference Between SUMIFS and DSUM in Google Sheets on my website, Crawlan.com.
How to Use the DSUM Function in Google Sheets
To help you get started, let’s dive right into how to effectively use the DSUM function in Google Sheets. Before we proceed, I recommend creating a sample database table like the one shown below:
Maintaining the data range reference B5:D13 will allow you to easily follow and adapt to my formula explanations. If you prefer, you can also duplicate my sample sheet by clicking the button provided.
Now, let’s take a closer look at how the DSUM function works.
Google Sheets DSUM Function Syntax Explained
The syntax of the DSUM function in Google Sheets is as follows:
DSUM(database, field, criteria)Here’s a breakdown of the arguments:
database: This represents the data range to consider. The first row should contain labels for each column’s values. In our example, the range B5:D13 is the “database,” with labels in the first row (B5:D5).field: Indicates which column in the database contains the values to be extracted and operated on. In our example, column 3 is used as the field, representing the numeric values. The formula will sum this column based on the given criteria. You can use either the column number directly (e.g., 3) or the column name within double quotes (e.g., “Sales Value”).criteria: The range containing conditions or criteria for filtering the database values before operating. It should include at least one column name (field label) and one cell below it. You can copy-paste column names into another range for criteria and fill only the required ones. In our example, the criteria are within the range B2:C3.
To make things easier to understand, here’s a screenshot illustrating the syntax of the DSUM function in action:
Examples
Assuming you have prepared the sample “database” in the range B5:D13 mentioned earlier, the next step is to specify the criteria. Follow these steps:
- Copy the labels “Name of Sales Person,” “Area,” and “Sales Value” from B5:D5.
- Paste these labels into B2:D2.
Let’s say we want to total the Sales Value of the person “Myron Ambriz” in the “North” area using the DSUM function in Google Sheets. To set this criteria, enter “Myron Ambriz” in cell B3 and “North” in cell C3. Your criteria should look like this:
| A | B | C | |
|---|---|---|---|
| 1 | Name of Sales Person | Area | |
| 2 | Name of Sales Person | Area | |
| 3 | Myron Ambriz | North | |
| 4 |
Next, in cell D3, enter the DSUM formula, considering multiple criteria:
=DSUM(B5:D13, 3, B2:C3)In this formula, B5:D13 represents our database, 3 is the field (the column to sum), and B2:C3 is the criteria range.
Note: You can replace the field #3 (column number) with the cell reference D2, which contains the column name. This provides flexibility in using either the column number or the column name for the DSUM function.
If you insert this formula in cell D3, you should get the result as $1,000.
Now, let’s expand the criteria and observe the result:
Formula:
=DSUM(B5:D13, 3, B2:C4)Result: $3,000.
Through these examples, you can see how powerful and flexible the DSUM function is in both Google Sheets and Microsoft Excel.
Can I Use DSUM Criteria Inside the Formula in Google Sheets?
Absolutely! However, the approach differs compared to how we handle SUMIF or SUMIFS criteria.
Take a look at the last formula criterion in the range B2:C4 above. It can be replaced with the following virtual array inside the formula:
{{"Name of Sales Person"; "Myron Ambriz"; "Myron Ambriz"}, {"Area";"North";"South"}}This formula part creates a virtual array containing the criteria and column names (field labels). Curly braces are used to create the array, and it has two parts: Vertical Array 1 and Vertical Array 2.
- Vertical Array 1: {“Name of Sales Person”; “Myron Ambriz”; “Myron Ambriz”}
- Vertical Array 2: {“Area”; “North”; “South”}
The outer curly braces and the comma separator stack these two vertical arrays horizontally.
The complete formula will be:
=DSUM(B5:D13, 3, {{"Name of Sales Person"; "Myron Ambriz"; "Myron Ambriz"}, {"Area"; "North"; "South"}})For forming the virtual array, instead of curly braces, you can use the VSTACK and HSTACK functions. Then the criteria will be:
HSTACK(VSTACK("Name of Sales Person", "Myron Ambriz", "Myron Ambriz"), VSTACK("Area", "North", "South"))For more in-depth information on using multiple conditions in the DSUM function, you can refer to my tutorial: AND, OR in Multiple Criteria DSUM in Google Sheets (Within Formula).
Date, Number, and Text Criteria in DSUM in Google Sheets
In our example, all the criteria are text strings since our sample database doesn’t have a date field. However, you might need to use date, timestamp, and number criteria along with comparison operators in real-world scenarios.
To gain a comprehensive understanding of using criteria in DSUM, I recommend referring to my tutorial: How to Properly Use Criteria in DSUM in Google Sheets.
Also, check out the additional resources below:
- The Ultimate Guide to Using Criteria in Database Functions in Google Sheets
- How to Use Multiple Sum Columns in the DSUM Function
- How to Do a Case Sensitive DSUM in Google Sheets
- Exact Match of Criteria in Database Functions in Google Sheets
- How to Use Date Difference As Criteria in DSUM in Google Sheets
With these resources at your disposal, you’ll become a pro at utilizing the DSUM function to its fullest potential in Google Sheets.
Remember, mastering DSUM opens up a world of possibilities for efficient calculations and data analysis in your spreadsheets. So dive in, experiment, and enjoy the power of DSUM!
