Using DSUM with multiple criteria is easy when referencing criteria from cell values. However, when using hardcoded criteria, you must correctly structure the criteria array within the formula.

Understanding AND and OR Logic in DSUM

This isn’t about using the AND and OR functions inside DSUM. Instead, it’s about handling multiple criteria in the same column or across different columns.

• OR Logic: Multiple criteria in the same column
• AND Logic: One criterion per different columns
• AND + OR Logic: A combination of both

Let’s break it down with simple examples below.

Sample Data

We’ll use the following dataset where students’ marks in different subjects are recorded across terms:

To apply AND and OR logic in DSUM with multiple criteria, we will filter based on Name and Term.

DSUM with a Single Criterion in Google Sheets

Suppose you want to sum the Maths marks for Student 1.

Criterion as Cell Reference

1. Enter "Student 1" in cell G2, below the label "Name" in G1.
2. Use this formula:=DSUM(A1:E7, "Maths", G1:G2)

Explanation

• Database: A1:E7 (data range)
• Field: "Maths" (the column to sum)
• Criteria: G1:G2 (filters the data)

If you leave G2 empty, the formula returns the sum of all students.

Criterion within Formula (Hardcoded)

Instead of using cell references, you can hardcode the criteria using VSTACK:

=DSUM(A1:E7, "Maths", VSTACK("Name", "Student 1"))

VSTACK creates a vertical array, replicating the criteria range within the formula.

DSUM with Multiple Criteria in Google Sheets

OR Condition in Multiple Criteria DSUM

When using multiple conditions in the same column, DSUM applies OR logic automatically.

Criteria as Cell Reference

1. Enter student names below "Name" in G1, e.g., "Student 1", "Student 2", "Student 3".
2. Use this formula: =DSUM(A1:E7, "Maths", G1:G4)

This formula sums Maths marks if the Name is “Student 1” OR “Student 2” OR “Student 3”.

Criteria within Formula (Hardcoded)

=DSUM(A1:E7, "Maths", VSTACK("Name", "Student 1", "Student 2", "Student 3"))

AND Condition in Multiple Criteria DSUM

If all conditions are met, DSUM applies AND logic.

Criteria as Cell Reference

1. Enter “Student 1” in G2 and “Second” in H2. Assume the relevant field labels are present in G1:H1.
2. Use this formula: =DSUM(A1:E7, "Maths", G1:H2)

This sums Maths marks only if Name = “Student 1” AND Term = “Second”.

Criteria within Formula (Hardcoded)

=DSUM(A1:E7, "Maths", HSTACK(VSTACK("Name", "Student 1"), VSTACK("Term", "Second")))

Explanation

• VSTACK("Name", "Student 1"): Creates a column for Name criteria
• VSTACK("Term", "Second"): Creates a column for Term criteria
• HSTACK(...): Combines them into a 2D array for DSUM

AND + OR Condition in Multiple Criteria DSUM

Now, let’s mix AND and OR logic.

Example: Sum Maths Marks for Student 1 in Term 1 OR Term 2

If this table is in G1:H3, the formula is:

=DSUM(A1:E7, "Maths", G1:H3)

Criteria within Formula (Hardcoded)

=DSUM(A1:E7, "Maths", HSTACK(VSTACK("Name", "Student 1", "Student 1"), VSTACK("Term", "First", "Second")))

This sums Maths marks where Name = “Student 1” AND Term = (“First” OR “Second”).

Exact Match in Multiple Criteria DSUM

By default, DSUM performs partial matches. "Student 1" would match "Student 11".

To enforce exact match, use =Student 1 instead of Student 1.

Example:

=DSUM(A1:E7, "Maths", VSTACK("Name", "=Student 1"))

Note: When entering this criterion in a cell, you may encounter issues. To avoid this, start with an apostrophe ('), like '=Student 1.

Final Thoughts

You’ve now learned how to use AND and OR in multiple criteria DSUM in Google Sheets. Whether you need to sum values based on a single condition, multiple OR conditions, AND conditions, or a mix of both, DSUM provides a flexible solution.

Key Takeaways

• OR condition: Multiple values in the same column
• AND condition: One value per different column
• AND + OR condition: Multiple criteria in multiple columns
• Hardcoded criteria: Use VSTACK and HSTACK

For structured filtering and summing, DSUM is an efficient alternative to SUMIFS.

Further Reading

• How to Do a Case-Sensitive DSUM in Google Sheets
• Properly Using Criteria in DSUM (Chart)
• Compare SUMIFS, SUMPRODUCT, and DSUM
• Using Multiple Sum Columns in DSUM
• Exact Match in Google Sheets Database Functions