Welcome to issue 7 of the Sheets Insiders membership.
You can see the full archives here.
This week, we’re looking at the database functions.
Relatively unknown, these are powerful and easy-to-use functions that are worth learning about.
Below, you’ll find out how they work and when you might want to use them.
Live Session Replay
Template
Download the Tables Template
Click on “Use Template” in the top right corner to make your own copy.
There is no Apps Script with this template.
Introduction
Have you ever heard of the Database family of functions?
I suspect 99.9% of us are sleeping on these old-school but incredibly powerful data functions.
There are 12 database functions, denoted by the “D” at the front of the function name. For example, DCOUNT to count records, or DSUM to sum values.
Why use database functions?
Here are some of the benefits of why you might use database functions:
- Easy to use (once you know how!)
- Flexible because the criteria are visible
- Great for non-technical people because the criteria are visible
- Work well with Tables & Dropdowns
- Fast and scalable
Consider this dataset:
It’s in a Table format named “data”.
That means we can use structured table references (as we saw in last week’s Sheets Insiders 6 issue) in our formulas.
Single Criteria Database Functions
The simplest scenario is when we have a single criteria, e.g. do a calculation with data for “Group C” only.
The database function filters the data and performs the operation on the filtered data.
They all take the same form: (database, field, criteria)
The database is our structured Table. We need to use the [#All] designation to include the header row.
We reference the field to use for the calculation, e.g. “Values”.
And then we point to the table with the filter criteria:
- The column heading to filter, e.g. “Group”
- The filter value, e.g. “Group C”
We set it up like this in cells B5:B6:
Our database formula looks like this:
=DCOUNT(data[#ALL],"Values",B5:B6)
All the database functions take the same format, for example, this sum formula:
=DSUM(data[#ALL],"Values",B5:B6)
Multiple Criteria Database Functions
AND Criteria
AND Criteria means the data must satisfy condition 1 AND condition 2 AND condition 3 etc.
We create AND criteria by add additional conditions across a row.
In this example, we have a row of column headers and a row of conditions, set up in the range C5:F6.
The database formula only counts values in the main data table that satisfy all the conditions.
The formula is:
=DCOUNT(data[#ALL],"Values",C5:F6)
in our Sheet:
What this means is that there are 112 rows of data in the main data table (out of 1000) that are:
- from Group C, AND
- less than $5000, AND
- happened on or after 1 December 2024, AND
- the invoice number is “XYZ-10”
OR Criteria
OR Criteria means the data must satisfy condition 1 OR condition 2 OR condition 3 etc.
We create OR criteria by adding additional conditions down a column.
In this example, we want to count values from any of the 3 invoices listed in table from C5:C8
=DCOUNT(data[#ALL],"Values",C5:C8)
In our Sheet:
There are 1,842 values (or rows of data in our main table) from invoices XYZ-10, XYZ-17, or XYZ-22.
AND + OR Criteria
We can combine this concept of rows and columns to do both AND and OR filtering.
We can also repeat columns to do more complex filter situations, or leave values blank to not filter.
=DCOUNT(data[#ALL],"Values",C5:G8)
In our Sheet:
This means there are 151 values (or rows of data in our Table) that satisfy either all the conditions on row 6, or all the conditions on row 7, or all the conditions on row 8.
Use a Table for the criteria
The database formulas become even more flexible if we put the criteria into a named Table too.
Then, if we add new rows or columns of criteria, they are automatically included in the formula criteria range. It saves us having to also update the range references inside the formula.
The formula is simplified to:
=DCOUNT(data[#ALL],"Values",criteria[#ALL])
Lovely!
Wildcard Criteria
One final thing to mention is that we can use wildcards in the database functions.
The star * matches zero or more characters.
The question mark ? matches exactly one character.
The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards.
So, to search for all invoices that start with “XYZ” in the dataset above, we can use this criteria:
XYZ*
For invoices that start with A and have a C as the third character, we would use:
A?C*
Useful for complex filtering with the database functions.
Why would you not use Database functions?
- They take up valuable real estate (rows & columns) in your Sheet
- They are not well known so others might not understand them
- They perform calculations only; they do not return data
- There are other ways to work with data that you might be more familiar with, e.g. Pivot tables, QUERY function, FILTER function, SUMIF, SUMIFS, COUNTIF, etc.