How to use DSUM Function in Google Sheets [Best Guide]

Hi. In this article, we will learn how to use DSUM function in google sheets. DSUM function is used for sum, but it’s more than a simple SUM function because it works on a table-like range only. You have to pass the entire data table including headers/labels when working with DSUM. The other difference is, that you can specify a condition to get the sum.

For example, sum only if the city is Tokyo or any other criteria. You may be thinking that it’s a SUMIF, or SUMIFS-like function, yes it can be compared with SUMIF and SUMIFS, but there are some differences, the main difference is DSUM syntax is very short and very clear, whereas the SUMIFS is very complicated to apply, read, and understand. DSUM is more like a query that is read easily and can be understood very quickly. Today we are going to learn the DSUM function and we will see some possible use cases of it with the help of a sample data set.

Use Cases of DSUM Function in Google Sheets

We have covered the IFS function in this series of google sheets, and if you remember the syntax was extraordinary long and there are high chances of errors, similarly SUMIFS has a problem with the syntax. It is pretty long and very difficult to read. So, for the solution, we have the DSUM function which works relatively similar to SUMIFS but has a very short and easy-to-understand syntax.

In DSUM we have the option to write the condition inside the formula or externally use it using the cell reference. We can define criteria by using a criteria table or by passing within the function block in Array notation. We will see both methods, and we will see how to use both of these methods to keep the syntax easy and clear to read. We can use multiple criteria, and other logical operators like AND, and OR, and also, we can use conditions like greater than, less than, and equal to define our condition. Notable thing is that the DSUM function only works on databases (structures data) or you can say table-like data only.

Step-by-step procedure to learn – How to use DSUM Function in Google Sheets?

In this section, we will see a step-by-step procedure to learn the DSUM function and we will see and implement various examples to learn it. Let’s see a simple example of the DSUM function

How to use DSUM Function in Google Sheets – DSUM Simple Usage

A simple usage of the DSUM function is when we use a single criterion and no logical operator, let’s see the basic example then we will move to some complex problems.

For this example, we need to have some sample data to perform the function on it.

Before that let’s see the syntax of the DSUM function

Syntax

=DSUM(database, field, criteria)

Here,

database: It is an array or range containing the overall database to consider, structured in such a way that the first row contains the labels for each column’s values.

field: It indicates which column in the database contains the values to be extracted or to be worked with or the given condition to be operated on, the field may either be a text label corresponding to a column header in the first row of the database or a numeric index indicating which column to consider, where the first column has the value 1.

criteria: It is an array or range containing the criteria based on which the data is to be filtered before starting working on

Since we have now got an understanding of the syntax let’s implement a simple example of the DSUM function

Step 1

Sample data

Note: You must have the data labels for every column

Step 2

The criteria table

Step 3

Writing the formula in some cell

Step 4

Pass the first argument (database)

Step 5

Pass the second argument (field)

Step 6

Pass the third argument (criteria)

Step 7

Press Enter key and you’re done

This is how to use DSUM function in google sheets with a very basic application of it.

Now let’s see if we have some other conditions/criteria to check on.

How to use DSUM Function in Google Sheets – DSUM with Multiple Criterion

Here, we will learn how to use DSUM function in google sheets with multiple criteria, in the previous example we had a condition, here we have another condition or we may have more than one condition to be satisfied by the data column to be considered as a valid data for the criterion. In this section, we will see different criteria and multiple conditions inside the DSUM function.

Previously we found the sum of monthly salary for the employees who are from the dept Quality.

Now we see some other conditions and will use multiple conditions together, like the sum of a column where the city is x and designation is y

Step 1

Add other criteria in the criteria table

Step 2

To add another condition, you need to write the column name above the value under that heading

Note:Now the logic is, sum all where Desg = “Manager”, and City=”Albany”, the sum will be performed for the employee’s month salary only where these conditions are satisfied.

Step 3

Write the DSUM function for the above logic

Step 4

Press Enter and you get the sum for the employee’s monthly salaries where both of the given conditions are satisfied.

Similarly, you can filter out the data by adding more conditions to it, you can use as many conditions as you want, you just need to add correct column headers in your criteria table and pass in the function as the third argument.

Now we can further work with the logical operators like OR AND, we have already seen AND in this section by using multiple conditions together. Let’s see the OR operator.

How to use DSUM Function in Google Sheets – OR Operator

Here we will implement the OR condition, we will use criteria where we will compare using an OR condition between the values, we will say like sum all hourly rate if the city is x or y, means we will give both options, and the SUM will be performed, if any of the given condition is satisfied.

Step 1

Add appropriate criteria in the criteria table

Step 2

Write the DSUM Function

Step 3

Pass the arguments

Step 4

Press Enter key and you’re done.

This is how you can use the OR operator to perform SUM based on the condition.

Let’s see an AND example as well.

How to use DSUM Function in Google Sheets – AND Operator

Let’s say you want to perform two checks on a single value, for example, you want to sum the total of the month’s salary where the hourly rate is more than 20 but less than 30. Does it make sense? Let’s do this using AND operator.

Step 1

Add the condition in your criteria table

Step 2

Write the DSUM functions and pass the arguments

Step 3

Press Enter and you have got the desired result

How to use DSUM Function in Google Sheets – With Array Function and without Criteria Table

This is easy and simple using the criteria table right, but for some users, changing the criteria table for every condition my gone annoying. So, we have a very good option with DSUM function to avoid the criteria table and pass the criteria within the function arguments. This will remove the criteria table and you will be working only with the function just like other functions

Step 1 (just optional)

Remove the criteria table

Step 2

Start writing the DSUM function

Step 3

Pass the first two arguments as you passed in previous sections

Step 4

Pass criteria after the comma inside the curly braces, and each value in double quotes. You will pass all the headers first (here two), then a semi column to tell sheets that now I am going to write the values for them, similarly inside double quotes.

Step 5

Press Enter key and you’re done.

This is how you can avoid the criteria table if it feels annoying to you. I like the criteria table method more than the array function method. You can use both works equally.

I hope you find this article helpful, and that you have learned how to use DSUM function in google sheets.

Download/Copy Google Sheets Workbook

Important Notes

Conclusion

So, in this article, we have learned how to use DSUM function in google sheets. We have learned what is DSUM function and how it’s different from SUMIF and SUMIFS. We have seen what is criteria table in the DSUM function and how we can perform a sum based on a given condition, or multiple conditions which are called criteria in the context of DSUM. We discussed several examples of DSUM functions using AND OR operators, less than, greater than, and equal to conditions, and finally, we discussed how to avoid the criteria table and use the array function to pass the complete criteria in English within the function. This way can be a little complicated for new users but it’s very simple and easy.

I hope you find this article helpful and that you have learned something new. Hit the like button and don’t forget to share this helpful article with your buddies. I will see you soon with another helpful tutorial till then take care. Thank you, so much & Keep learning, with Office Demy.