There are 12 database functions in Sheets which start with the letter ‘D’. I have covered most of them in my Google Sheets functions guide. In this guide, I am paying my attention mainly on how to correctly use criteria in database functions in Google Sheets.

Introduction to Database Functions

All the database functions, whether it’s DSUM, DPRODUCT or any other D Function, has the same number of common arguments. They are database, field, and criteria.

Even though our topic is revolving around the criteria, you must know the other two arguments in use.

Note: If you happen to see D Functions or Dfunctions on this tutorial, please read it as database functions.

Database:

As you may know, the database is the range/array to use to compute which must contain field labels in the first row (refer the image below).

If your range/array contains 4 columns, that means you have a database with four fields.

Example to Database (Structured Data) for using in Database Functions:

Similar: Column Heading | Column Label | Column Name | Field | Field Label in Google Sheets.

Field:

Field indicates, except in DGET, which field contains the values to be aggregated. In DGET, the field is the column to return the output.

To express the field, you can either use the field name or column number. In all of the formulas I am going to use the column number not field name.

In the above example, you can use “Amount” or 4 as field number to aggregate the value in the fourth field.

Criteria:

Explained in detail below.

How to Use Criteria in Database Functions in Google Sheets

Unlike other Spreadsheet functions, the criteria must be structured in D Functions. That means you must include the field names with the criteria.

The criteria can be an embedded array like below.

Also, the criteria/conditions can be an array generated by an array expression.

={"Customer Name";"Customer 2"}

So in this tutorial, I am going to explain how to use the criteria in database functions in detail.

I will try to explain how to properly embed criteria as well as how to generate complex criteria using array expression.

Database Functions in Google Sheets with Single Criteria

First let me share with you the common syntax of the Database functions. Replace Dfunction with the database function of your choice.

DFunction(Database, Field, Criteria)

Single String Criteria

Database: For the sample database please take a look at the range A1:D10 in the above screenshot.

Field: I am using the fourth column, i.e. the number 4 as the field.

Criteria: On the same screenshot I have embedded the criteria in the range F1:F2.

For the example, I am using the DSUM function here.

=dsum(A1:D10,4,F1:F2)

The same DSUM formula but criteria generated by array expression.

=dsum(A1:D10,4,{"Customer Name";"Customer 2"})

Single Date Criteria

=dsum(A1:D10,4,F1:F2)

When you use array expression, please use the DATE function instead of directly entering the date.

I am recommending this;

=dsum(A1:D10,4,{"Date";date(2019,3,25)})

Not this. But both will work.

=dsum(A1:D10,4,{"Date";"25/03/2019"})

Single Numeric Criteria

Sum field # 4 if the invoice value is 6000.00.

=dsum(A1:D10,4,F1:F2)

Equivalent DSUM Formula:

=dsum(A1:D10,4,{"Amount";6000})

Database Functions with Multiple Criteria in Google Sheets

In the earlier examples, I have used the DSUM function. It doesn’t matter which D function you use, the criteria usage tips are the same. So let’s begin with DMAX here for a change.

Multiple Conditions in Same Field (Similar to OR condition)

=dmax(A1:D10,4,F1:F3)

In this formula, I have used two conditions in the field “Customer Name”. The formula filters the records (rows) of “Customer 1” and “Customer 2”. Then finds the Max in field 4.

This is similar to the OR criteria use as the conditions are from the same column. It’s a statement like “this or that”.

You can code the same formula as below to include the criteria within.

=dmax(A1:D10,4,{"Customer Name";"Customer 1";"Customer 2"})

Please note that the semicolon places the values in different rows in the same column. When you want to place the conditions in two different columns, use the comma as the separator.

Must Read: Combined Use of IF, AND, OR Logical Functions in Google Sheets.

Multiple Conditions from Different Fields (Similar to AND Condition)

Before going to the next example on how to use criteria in database functions in Google Sheets, you must know how to use array expression to form multiple criteria.

Suppose the above is our next embedded criteria range. How to use an array expression to use within the formula. To understand that please follow the below steps.

Step 1:

Array expression to place the values in F1:F4 in rows in the same column.

={"Customer Name";"Customer 1";"Customer 2";"Customer 3"}

Step 2:

Array expression to place the values in G1:G4 in rows in the same column.

={"Date";DATE(2019,3,25);DATE(2019,3,25);DATE(2019,3,25)}

Step 3:

Combine both the arrays (Step 1 and Step 2). How?

Since we want two columns, use comma as the separator between the above two arrays.

={{"Customer Name";"Customer 1";"Customer 2";"Customer 3"},{"Date";DATE(2019,3,25);DATE(2019,3,25);DATE(2019,3,25)}}

We can use this array expression in the below formula.

=dsum(A1:D10,4,F1:G4)

The above formula filters the records of “Customer 1”, “Customer 2” and “Customer 3” if the corresponding Date column contains the date 25/03/2019.

I have used the DSUM function to sum the filtered field # 4.

Equivalent Formula:

=dsum(A1:D10,4,{{"Customer Name";"Customer 1";"Customer 2";"Customer 3"},{"Date";DATE(2019,3,25);DATE(2019,3,25);DATE(2019,3,25)}})

How to Use All the Comparison Operators in D Functions in Docs Sheets

There are five comparison operators that you can use together with the D Functions in Google Sheets. They are;

= Equal to.
< Less than.
> Greater than.
<> Not equal to.
<= Less than or equal to.
>= Greater than or equal to.

All the above formulas contain the Equal to comparison operator! Yes, there is no need to use it with the criteria in Database functions.

We can use all the other comparison operators in numeric as well as date fields in Database functions in Google Sheets.

I am using a date field here.

Problem: Filter “Customer 2” in field 1 if the invoice dates are >25/03/2019 in field 3. Then find the average in field 4.

To find the average in a database, we can obviously use the DAVERAGE function.

Formula:

=daverage(A1:D10,4,F1:G2)

Formula Equivalent:

=daverage(A1:D10,4,{{"Customer Name";"Customer 2"},{"Date";">"&date(2019,3,25)}})

Similarly, you can use other comparison operators. But sometimes you may want to filter a date range in Database functions.

See that example below.

Here I want the D Function to filter the records between the date range 25/03/2019 to 15/04/2019, both the date exclusive. Then find the average of the values in field 4.

In such cases arrange the criteria as above in two columns, not in one column as below.

The below criteria means date greater than 25/03/2019 or date less than 15/04/2019. What we want is date greater than 25/03/2019 and date less than 15/04/2019.

That’s all. Enjoy!