The use of multiple criteria in DSUM is easy if the criteria are in the form of cell references. That is the charm of database functions. But we can also use multiple criteria DSUM in Google Sheets with conditions included within the formula. You can master that ‘art’ in this new Google Sheets tutorial.

Learn all about the multiple criteria DSUM in Google Sheets including the AND, OR in the criterion. I am talking not about the logical AND, OR operators but the equivalents. You can understand that in the formula section below.

I have included enough examples to make you master the multiple criteria DSUM in Google Sheets. Here we go!

DSUM Database Function in Google Sheets

Syntax:

DSUM(database, field, criteria)

Here are some DSUM advanced formula examples with single and multiple criteria use.

First I am going to share with you the Dsum criteria use as the cell reference. Then you will get the equivalent criteria tips to use within the formula. Let’s use the sample data in the table below.

DSUM with Single Criterion in Google Doc Sheets

Criterion as Cell Reference:

=DSUM(A1:E7,3,G1:H2)

As per the syntax I have marked the database and criteria on the screenshot. Regarding the field, it’s the column number to sum. I have used 3 as the field number which is the ‘maths’ column.

In this example, there is only one criterion. Below is the equivalent DSUM formula where the criterion is within.

Criterion within Formula:

=dsum(A1:E7,3,transpose({"name","Student 1"}))

Actually, the Transpose is not a must here. But I have used it because in multiple criteria DSUM you will find it useful.

=transpose({"name","Student 1"})

To see what it does, enter the above formula in a blank cell. It will return the criteria as below.

As a side note, the same you can achieve without using the Transpose function as below.

={"name";"Student 1"}

You can use either of the formulas to generate an array to use as the criterion.

If you are not familiar with creating arrays, please read this – How to Use Curly Brackets to Create Arrays in Google Sheets.

DSUM with Multiple Criteria in Google Sheets

The use of AND, OR logical operator equivalents comes in the multiple criteria DSUM formulas.

In the real sense, there is no such operator use in DSUM as it’s a database function. But we can achieve the same by using the criterion correctly.

OR Condition in Multiple Criteria DSUM in Google Sheets

Normally when we are using multiple conditions in the same column, without knowing, we are using the OR condition (this or that) in DSUM. See the examples.

Criteria as Cell Reference:

=dsum(A1:E7,3,G1:H4)

This formula sums column C if values in column A are “Student 1”, “Student 2” or “Student 3”.

You can rewrite the above formula as below. In that, the criterion is a virtual array.

Criteria within the Formula:

=dsum(A1:E7,3,transpose({"name","Student 1","Student 2","Student 3"}))

AND Condition in Multiple Criteria DSUM in Google Sheets

If all the provided conditions are met, then the formula would return the correct output else 0.

Criteria as Cell Reference:

The formula sums column C if the column A value is “Student 1” and the column B values is “Second”.

Formula:

=dsum(A1:E7,3,G1:H2)

Criteria within Formula:

See how I am recreating the above multiple criteria within the DSUM formula.

=dsum(A1:E7,3,transpose({"name","Student 1";"term","Second"}))

I know by now you may have already mastered one technique, i.e. the use of AND, OR in DSUM in Google Sheets.

To polish what you have learned here is one more formula. In that, I am using AND, OR together in DSUM.

AND, OR Condition in Multiple Criteria DSUM in Google Doc Sheets

Here you may face one issue with the virtual criterion (criterion within formula).

=dsum(A1:E7,3,G1:H3)

This formula sums column 3 if the “name” of the student is either “Student 1” or “Student 2”. Also for the “Student 1“, the “term” must be “First” in column 2. For Student 2, the term is not applicable.

You can use the following formula to recreate the criteria.

transpose({"name","Student 1","Student 2";"term","First",""})

This replaces the criteria in G1:H3. Since there is no value in cell H3, I have used double quotes (equal to blank) at the end of the formula. Because in the virtual range the number of rows must match in both the columns.

=dsum(A1:E7,3,transpose({"name","Student 1","Student 2";"term","First",""}))

Conclusion

I have included lots of tips and tricks in this spreadsheet tutorial to make you master the Google Sheets DSUM function. But you may have seen I have missed one tip, i.e. how to sum multiple fields in DSUM.

In all the above formulas I have only used field # 3, i.e. the “maths” column to sum. In the next post, I’ll try to share with you how to use multiple fields in DSUM.

Update: See the 6th link under ‘Resources’ below.

Resources:

1. How to Do a Case Sensitive DSUM in Google Sheets.
2. How to Properly Use Criteria in DSUM in Google Sheets [Chart].
3. Compare Sumifs, Sumproduct, Dsum with Example in Google Sheets.
4. Difference Between SUMIFS and DSUM in Google Sheets.
5. How to Use Date Difference As Criteria in DSUM in Google Sheets.
6. Google Sheets: How to Use Multiple Sum Columns in the DSUM Function.
7. Exact Match in Database Functions in Google Sheets – How-To.
8. The Ultimate Guide to Using Criteria in Database Functions in Google Sheets.