Imagine working with a large e-commerce sales dataset, where every row represents an individual sales purchase. You might want to analyze this data in different ways: grouping by customer, product category, and location or applying complex conditions (e.g., counting orders with product X when the Sales Amount is > $1000). While Excel functions like SUMIFS(), COUNTIFS(), and AVERAGEIFS() can handle some of these conditional calculations, they can get complicated, especially when dealing with multiple AND and OR conditions. This is where database functions shine. In this article, we will explore the awesome functionalities of some of Excel’s database functions.

Database functions in Excel have been around for quite some time, yet they often go unnoticed. These functions are not only easier to use but also more powerful than popular conditional aggregate functions like SUMIFS() and COUNTIFS(). They even get around some of the limitations of these functions. They work particularly well with tabular data and Excel tables where each column represents a specific field or attribute, and each row is a unique record or entry.

The Database Functions Family:  

In the list below, you will notice that there is a database function for almost all aggregation

One great advantage of the database function is that they all have the same syntax which is made up of just three arguments: database, field and criteria. Additionally, they work with mathematical operators such as < and > and wildcards.

The database Argument

Database is a reference to a range of cells containing data including the headers. To use numbers or dates as headers, identify them as text by entering a leading apostrophe (‘). The column headers of your table must match the headers in the field and criteria so it’s best to make them simple and descriptive, with no extra spaces, punctuation marks or symbols.

The field Argument

This represents the column within the database that contains the data on which you want the function to perform the calculation. In the database function, you can choose to reference the field/column name in quotation marks or a number corresponding to the column’s position within the database: the first column is 1, the second column is 2, etc. You may also enter a reference to a cell that contains the column’s label or number. Amazing right!

The criteria Argument

Criteria here refers to a range of cells, typically separate from database, that specifies the search conditions. The top row of this range contains headers matching the database’s column headers. The cells below these headers define the criteria that records must meet to be included in the function’s calculation.

The criteria can be numbers, text, or blank cells. You can input these directly or use formulas and functions to calculate them. Cell references are also supported.

How It Works

We will explore some examples of the database functions. First, we need two tables; the database table and the criteria table. To practice, use this link to access the file

Example 1: How DSUM works

DSUM works very similar to how SUMIFS() work. It sums up a column based on the criteria you have set. In this example, we would like to calculate the total sales for Kentucky.

Syntax: =DSUM(Database, field, criteria)

The database here refers to the large table you are working with, which we will reference as Database1 using a named range.

The field is the sales column which can be referenced as K8 or “Sales”

Lastly, the criteria here would be the reference criteria array/table as seen below.

Bringing it all together, we have:  =DSUM(Database1,”Sales”,K4:L5)

Example 2: Simple AND Criteria using DSUM

Syntax: =DSUM(Database, field, criteria)

Suppose you want to calculate the total sales for office supplies in Califonia using the snapshot (Database table) below:

For the database argument, we reference the entire table, using the named range – Database1.

Next, the field argument would be the Sales column which can be written in quotation or referencing the cell and then the criteria argument references the criteria array/table shown below:

Note that when working with AND condition, they go across the columns as shown above. Read as Office Supplies AND California

Bringing it all together, the formula would be written as:

=DSUM(Database1,”Sales”,$K$4:$M$5)

Note that a blank cell indicates that no tests should be performed.

Example 3: Simple AND Criteria using DCOUNTA

Syntax: =DCOUNTA(Database, field, criteria)

Here we are tasked to count the number of Orders that came from California with Sales above $1000. Using the same data set as above.

For the database argument, we reference the entire dataset referenced as – Database1.

For the field argument, we state the Order Item ID column as 1 (you can choose to reference the cell too). Lastly, we reference the criteria array and utilize mathematical operators for the criteria argument. When working with AND

Bringing it all together, the formula would be written as:

=DCOUNTA(Database1,1,$K$4:$M$5)

Example 4: Multiple OR criteria

What if we would like to calculate the average profit per Order Item ID for Fort Wort (City) OR for California (State)?

It’s quite easy, we just need to add another row to the criteria array. Let’s break things down.

Syntax =DAVERAGE(Database, field, criteria)

For the Database argument, we reference Database3 as shown below:

For the field argument, we reference the profit column in the database table using the column “profit” in quotation.

Lastly, for the criteria argument, we reference the criteria array/table adding a row to take care of the OR condition.

Together, we have =DAVERAGE(Database3,”profit”,$J$4:$H$6)

Example 5: Working with wildcards

It’s awesome that the Database functions allow wildcards. You can use * and ? to enhance your search. When you type a letter before the “? or *” it means you want to find words that begin with the letter e.g. N?  Or N* In a country column will call for all countries that start with the letter N.

Asterisks between words e.g. *CA* will search for countries that have “CA” in their name.

Using the database1 dataset, suppose we want to calculate the Total Sales of States that begin with F, the criteria will look like this:

Rules of Database Functions

1. When specifying the field in the formula, you can use either the column name in quotation or its corresponding number.
2. Your criteria table only requires columns for the fields you want to filter. No need to duplicate all columns from your dataset.
3. If you leave criteria table rows empty in a Database formula, the function will not apply any filters. It will perform the calculation i.e. SUM/COUNT/AVERAGE etc on the entire dataset, ignoring the specified conditions.
4. The criteria table can include formulas like <>TODAY(), drop-down lists, and other elements. Feel free to get creative with these features to add interactivity to your reports
5. The Criteria table is case-insensitive for both column headers and the criteria themselves.

Conclusion

We have seen how amazing and easy-to-use the database functions can be. The only shortfall would be that they require more space for the criteria table compared to array formulas like COUNTIFS() or SUMIFS(), However, the flexibility they provide makes it a worthwhile choice, especially when working with large datasets with multiple AND and OR criteria. Next time you need to perform conditional aggregation on large datasets in Excel, do well to try out database functions. Remember also, you can apply drop-down lists to your criteria table to make it even more interactive. Let me know in the comments when you try out the database functions.