What Is Google Sheets DSUM?
The Database SUM or the Google Sheets DSUM, helps users calculate the total of the selected values in a dataset, that satisfies a single or multiple criteria for the same or different columns. The DSUM In Google Sheets is an alternative to the SUMIF and the SUMIFS functions in Google Sheets.
For example, suppose we have data of products and their units sold. We will calculate the total of the laptop units sold using the DSUM in Google Sheets.
Select cell E5, enter the formula =DSUM(A1:B10,”Units”,D4:D5) and press “Enter”, as shown.
The output is “135”, i.e. the sum of the total values of 3 cells with the criteria as “Laptop”.
Key Takeaways
- The Google Sheets DSUM is a database function which returns the total of the selected numeric values only if it fulfills the given criteria.
- We have three arguments for the function where all three are mandatory arguments. We will get an error or the formula will not execute if even one of the arguments is not provided.
- Itis an alternative formula for the SUMIF and SUMIFS functions except that DSUM is database and supports queries too.
- A separate table known as the criteria table must be created apart from the dataset which helps us select the cell range and get right results. Or else we must add all the data as the cell value within double-quotes, which may result in errors.
- The criteria table in the data is not case-sensitive.
- We can use the sort and filter option to create a drop-down list is according to the criteria. As the drop-down list changes, it will show the updated results, accordingly.
Syntax
Thesyntax of the DSUM Formula in Google Sheets is,
Themandatoryarguments of the DSUM Formula in Google Sheets are,
- database: It is the cell range or the dataset selected in a way that the first row consists the field names.
- field: It is the column name we want to extract the data from and operate on.
- criteria: It is the range containing single or multiple conditions to check and filter the data.
How To Use DSUM Function In Gooogle Sheets?
We can use the DSUM Function In Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output – select the “Insert” tab – click the “Function” option right arrow –click the “Database” option right arrow – select the “DSUM” function, as shown below.
Step 2: The “DSUM” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =DSUM( in the cell, as shown below. [Alternatively, type =D or =DS and double-click the DSUM from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some of the Google Sheets DSUM examples that satisfies single or multiple criterias for same or different columns.
Example #1 – Single Criteria
The given dataset consists of smartphone models, their delivery dates and the units ordered. We will calculate the total units ordered of a specific model as a criterion in the created criteria range table.
The steps to calculate the SumTotal of a single criteria using the DSUM are as follows:
Step 1: Select cell D12 and enter the formula =DSUM(A1:C9,C1,A11:A12), as shown below.
[Note: A1:C9 is the dataset cell range that is selected with the column names, C1 is the column C field name, which can either be entered as a cell value in double-quotes or as a cell reference, and finally we have the criteria range A11:A12.]
Step 2: Press “Enter”, to get the calculated result, as shown below.
We see that the criteria is to check all the Apple models, regardless of the date and calculate the total of the ordered units.
Example #2 – DSUM with Multiple Criteria for the Same Column (OR)
Let us consider the Example #1 dataset, once again. We will apply DSUM with Multiple Criteria for the Same Column (OR).
The given dataset consists of smartphone models, their delivery dates and the units ordered. We will calculate the total units ordered of a specific model as a criterion in the created criteria range table.
The steps to calculate the SumTotal for multiple criteria using the DSUM are,
Step 1: Select merged cells B12:B13 andenter the formula =DSUM(A1:C9,C1,A11:A13), as shown below.
Step 2: Press “Enter”, to get the calculated result, as shown below.
We see that the criteria is to check all the Apple and Samsung models, same column multiple criteria, regardless of the date and calculate the total of the ordered units.
Example #3 – DSUM with Multiple Criteria for the Same Column (AND)
Let us consider the Example #1 dataset, again. We will apply DSUM with Multiple Criteria for the Same Column (AND).
The given dataset consists of smartphone models, their delivery dates and the units ordered. We will calculate the total units ordered between 2 dates using the greater than and the less than feature, in the created criteria range table.
The steps to calculate the SumTotal for multiple criteria using the DSUM are,
Step 1: Select cell G2 andenter the formula =DSUM(A1:C9,C1,E1:F2), as shown below.
Step 2: Press “Enter”, to get the calculated result, as shown below.
We see that the criteria is to check all the Apple and Samsung models, between the given dates and calculate the total of the ordered units.
Example #4 – DSUM with Multiple Criteria for Different Columns
The dataset given below consists of product details, their dealers and the respective sales for the years from 2019 to 2021. We will apply the DSUM with Multiple Criteria for Different Columns, as shown in the criteria range table in cells G3:H6.
The steps to calculate the SumTotal for multiple criteria using the DSUM are,
Step 1: Select merged cells I4:I6 andenter the formula =DSUM(A1:E10,D1,G3:H6), as shown below.
Step 2: Press “Enter”, to get the calculated result, as shown below.
The output is shown above, where the formula takes into account the different columns multiple criterias and calculates the corresponding total.
Important Things To Note
- If the criteria range is paced below the list ensure not to add information to the list, as it will push the cells down and return error. To avoid such situations, its better to add it at the right of the dataset, unless we do not add extra columns.
- The criteria range should not overlap the list. Its better to leave a row or a column in-between the dataset and the criteria table, if we are creating the table below or beside the dataset, respectively.
- If the specified criteria are not set, the function generates the sum of the overall column.
- We get “#Value” error, if one of the argument values is missing.
- We get the “#NA” error, if there is a mistake in the formula, indicating that the calculation cannot be performed.
- Ensure to add the direct cell values within double-quotes, to add as text. Or else the formula will be incorrect.
Frequently Asked Questions (FAQs)
The Google Sheets DSUM isn’t working because of the following reasons:
a. The argument values or cell reference are not organized properly.
b. The cell range selected is modified or deleted.
c. The cell value for the arguments given directly are not inserted within double-quotes.
d. Criteria range is created below the dataset and new data is added in the dataset.
We often forget in which category a function falls, here, the “DSUM” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “DSUM” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the DSUM in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “DSUM” function, as shown below.
Download Template
This article must help understand the Google Sheets DSUM formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Google Sheets DSUM. We learn its syntax and how to use it to find the sum of values with multiple criteria with examples. You can learn more from the following articles. –
Leave a Reply