There are 12 database functions in Google Sheets that start with the letter ‘D’. In this guide, I will focus on how to correctly use criteria in database functions, which is an essential aspect of working with these functions.
Introduction to Database Functions
Before diving into the topic of criteria, let’s first understand the common arguments of all database functions: database, field, and criteria. These arguments are crucial for the proper functioning of database functions.
Database refers to the range or array that you want to compute. It should contain field labels in the first row. For example, if your range has four columns, you have a database with four fields.
Field indicates which field contains the values to be aggregated. In most cases, you can use either the field name or column number to express the field.
Now, let’s focus on the main topic of this guide – criteria.
How to Use Criteria in Database Functions in Google Sheets
Unlike other spreadsheet functions, criteria in database functions must be structured. This means that you need to include the field names along with the criteria.
The criteria can be embedded within an array or generated by an array expression. Here’s an example of using an embedded array as criteria:
={"Customer Name";"Customer 2"}In this guide, I will explain in detail how to use criteria in database functions. I will cover both the proper embedding of criteria and the generation of complex criteria using array expressions.
Database Functions in Google Sheets with Single Criteria
Let’s start with understanding how to use a single criterion in database functions. The syntax for all database functions with a single criterion is as follows:
DFunction(Database, Field, Criteria)Single String Criteria
To illustrate this, let’s consider the DSUM function.
Suppose you have a sample database in the range A1:D10. The field you want to aggregate is the fourth column (column number 4), and the criteria are embedded in the range F1:F2.
You can use the following DSUM formula with the embedded criteria:
=dsum(A1:D10,4,F1:F2)Alternatively, you can generate the criteria using an array expression:
=dsum(A1:D10,4,{"Customer Name";"Customer 2"})Single Date Criteria
When using a date as a criterion, it’s recommended to use the DATE function instead of directly entering the date. Here’s an example:
=dsum(A1:D10,4,{"Date";date(2019,3,25)})Single Numeric Criteria
Suppose you want to sum field #4 if the invoice value is $6000.00. Here’s an example:
=dsum(A1:D10,4,{"Amount";6000})Database Functions with Multiple Criteria in Google Sheets
Now, let’s explore how to use multiple criteria in database functions. The tips and techniques are similar regardless of the specific database function used. For this example, let’s use the DMAX function.
Multiple Conditions in Same Field (Similar to OR Condition)
For this scenario, let’s say we want to find the maximum value in field 4 for records with “Customer 1” and “Customer 2” in the “Customer Name” field. Here’s the formula using DMAX:
=dmax(A1:D10,4,F1:F3)Alternatively, you can use an array expression to include the criteria within the formula:
=dmax(A1:D10,4,{"Customer Name";"Customer 1";"Customer 2"})Multiple Conditions from Different Fields (Similar to AND Condition)
To filter records based on multiple conditions from different fields, you can use array expressions. Let’s say we want to filter records for “Customer 1”, “Customer 2”, and “Customer 3” if the corresponding “Date” column contains the date 25/03/2019. Here’s the formula using DSUM:
=dsum(A1:D10,4,F1:G4)Alternatively, you can use the equivalent formula with the array expression included:
=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 Google Sheets
You can use various comparison operators in database functions. The common operators include:
- “=” (Equal to)
- “<” (Less than)
- “>” (Greater than)
- “<>” (Not equal to)
- “<=” (Less than or equal to)
- “>=” (Greater than or equal to)
When using these operators, there is no need to explicitly include the equal to operator “=” in the criteria.
To demonstrate the use of comparison operators, let’s consider a scenario where we need to filter records for “Customer 2” in field 1 if the invoice dates are greater than 25/03/2019 in field 3. We then want to find the average in field 4. Here’s the formula using DAVERAGE:
=daverage(A1:D10,4,F1:G2)Alternatively, you can use the equivalent formula with the criteria generated using an array expression:
=daverage(A1:D10,4,{{"Customer Name";"Customer 2"},{"Date";">"&date(2019,3,25)}})You can also use comparison operators to filter records based on a date range in database functions. Simply arrange the criteria in two columns, specifying the start and end dates as required.
That wraps up the ultimate guide to using criteria in database functions in Google Sheets. With these tips and techniques, you can effectively leverage database functions and make the most out of your Google Sheets experience.
For more useful tips and guides on Google Sheets and other topics, visit Crawlan.com.
