Master the DSUM Function in Google Sheets: Explained

In this article, we’ll dive deep into the DSUM function in Google Sheets. Get ready to uncover the secrets of this powerful formula!

How Does the DSUM Function Work in Google Sheets?

The DSUM function in Google Sheets is similar to the SUMIF or SUMIFS functions. It calculates the sum of selected entries in a database based on specific criteria. To use the DSUM formula, you need to prepare a dataset with column headers (table headers), just like the example dataset below:

When and Why Should You Use the DSUM Formula in Google Sheets?

The DSUM formula in Google Sheets is incredibly beneficial when you need to sum values in a large database or table based on specific criteria. It saves you time and effort compared to manually filtering and calculating data, especially when the database is extensive and constantly changing.

Here are a few examples where the DSUM formula can come in handy:

• Adding up sales amounts for a specific product within a date range.
• Totaling expenses for a particular category or account.
• Aggregating the number of units sold for a specific product or SKU.
• Summing up the hours worked for employees in a specific department or position.

How to Use the DSUM Function in Google Sheets?

To incorporate the DSUM formula into your Google Sheets, follow these steps:

1. Type “=DSUM(” or go to “Insert” → “Function” (or click directly on the “Functions” icon) → “Database” → “DSUM.”
2. Select the data range you want to analyze.
3. Choose the column header whose values you want to sum.
4. Enter one or more criteria that the elements must satisfy to be included.

Here’s the general syntax of the DSUM formula:

• database: This argument should be a range or array with the first row containing the table headers (or field names), followed by a value in each row (starting from the second row downwards) for each field. The “database” can resemble the table in the image below:

• field: This parameter should be one of the table headers in the “database,” and the values in this column that meet specific conditions are summed by the formula. This argument can be a text string (entered manually) of the table header or a column number of the column you want to refer to, assuming the leftmost column of the dataset is 1, or a cell reference containing the text string or column number.

• criteria: This argument should be a range or array with the first row containing the name(s) of the field(s), followed by specific condition(s) in the second row and downwards.

Now, let’s explore some examples of using the DSUM formula on the example dataset mentioned above. Suppose you want to calculate the total sales matching one or more criteria.

In the first example, imagine you need to calculate the total sales for Client A:

1. Select the entire table, including the row showing the table headers, for the “database” argument.
2. Enter the “field” by selecting the cell containing the column header whose column values that satisfy the “criteria” are aggregated.
3. Prepare the “criteria” range as depicted in the image below. Enter a table header in one cell and a condition beneath the cell containing the table header.

While we highly recommend using cell references in the DSUM formula as it makes the formula easy to understand and dynamic, if you want to use the DSUM function with manual inputs for the “field” and “criteria” parameters, you can input the formula as follows:

=DSUM(A2:D10, "Montant de vente", {{}} = "Client A")

Note that you need to surround text strings with quotes and input a table header and criterion separately, separated by a semicolon and enclosed in a pair of braces.

The second example showcases when the DSUM formula aggregates sales when “Client” is either “Client A” or “Client B.” Suppose you exclude cell address changes in the DSUM formula. The only difference between the first two examples is that the range for the “criteria” is extended one cell downwards, as shown in the image below:

As you can see, when you want to add one or more conditions for an existing column header used for the “criteria,” you input the additional requirements in cells just below the first condition. The formula with cell references looks like this:

=DSUM(A2:D10, "Montant de vente", {{}} = "Client A", {{}} = "Client B")

The third example presents the case where you sum sales matching two criteria. Imagine you want to sum the sales contracted with Client A on or after 2/4/2023.

Note that when you add a new table header in the “criteria,” you place it in the cell just to the right of the existing table header for the “criteria” and enter a specific condition beneath the cell containing the additional table header. The formula with cell references looks like this:

=DSUM(A2:D10, "Montant de vente", {{}} = "Client A", {{}} >= DATE(2023, 4, 2))

(The formula with manual inputs)

The last example showcases the DSUM function containing three criteria. Suppose you need to sum sales matching three conditions: (i) “Produit” is “Type X”, (ii) “Date du contrat” is on or before 2/7/2023, and (iii) “Montant de vente ($)” is equal to or greater than 3000. You can create the formula with cell references like this:

=DSUM(A2:D10, "Montant de vente", {{}} = "Type X", {{}} <= DATE(2023, 7, 2), {{}} >= 3000)

For the formula with manual inputs, you need to (i) surround each pair of table header and specific criterion with braces, (ii) separate each piece included between the braces with a comma, and (iii) surround all pieces separated by commas with a pair of braces.

What’s the Difference Between DSUM and SUMIFS?

The DSUM and SUMIFS functions in Google Sheets both sum values in a database or table based on specific criteria and yield the same results if their arguments are correctly input. However, there are a few differences between them:

• Dataset: To use the DSUM function, you need to organize your dataset so that each column of the data table contains a table header representing the column values, as all required arguments depend on these table headers as input (or as part of the input). On the other hand, when using the SUMIFS function, you don’t necessarily need to create a well-organized data table with column headers (although it’s highly recommended).

• Criteria: You need to include all conditions in one range or array as it only accepts a single argument for the “criteria” in the DSUM formula. However, in the SUMIFS formula, you need to incorporate pairs of criteria and the corresponding ranges for the criteria until you have included all conditions. Thus, if the volume of data is much larger, the DSUM formula may perform more efficiently than the SUMIFS function.

Now that you’ve mastered the DSUM function in Google Sheets, you can supercharge your data analysis skills. Start exploring the endless possibilities and make your spreadsheets work wonders for you!

For more insights and tips on leveraging Google Sheets’ capabilities, visit Crawlan.com. Happy spreadsheet crunching!