Hi ,

Easy Excel Database Functions

Excel Database functions have been around for donkey's years so it's surprising that not many people know about them. They work great with tabular data and Excel Tables. 

And they're not only easier to use and more powerful than the newer, more popular equivalent functions like SUMIFS, COUNTIFS etc., but they can get around some of their limitations.

Prefer to read this on our website? 

Then click this link >>> Easy Excel Database Functions

Video: Easy Excel Database Functions

Watch on YouTube Subscribe

Download the Cheat Sheet

In addition to the usual Excel workbook, I've prepared a Cheat Sheet as a handy reference to these database functions, and it's yours free to download.

Excel Database Functions

There is a database function for nearly every aggregation type, from average through to variance, as listed below. They're an alternative to SUMIFS, AVERAGEIFS, MINIFS etc. except more powerful.

Database Function Example 1 - How DSUM Works

DSUM syntax

=DSUM(database, field, criteria)

Taking the data below stored in an Excel Table called 'DataTable', let’s say I want to SUM the amounts for all Level 1 invoices:

The second argument for DSUM is the criteria. This is stored in cells in the workbook (on any sheet):

And the formula would be written:

=DSUM(DataTable[#All],"Amount",$G$6:$G$7)

Syntax Notes:

• database

= DataTable[#All]. This is a Table Structured Reference for my tabular data set including the column labels, hence [#ALL] in the reference. Alternatively, I could type in the cell range A4:E70 (including column labels).

• field

= "Amount". This is the name of the column I want to sum. This could also be the column number of the data table, in this example, 5.

• criteria

= $G$6:$G$7. This is the range of the criteria table

Database Function Example 2 - Multiple AND Criteria

Let’s say I want to SUM all Level 1 invoices for the period January – March 2021. When working with 'AND' criteria, they go across the columns in a single row like this:

Note: my dates are dd/mm/yyyy. Please change the dates in the criteria table to suit your Excel region settings.

Then my database formula is:

=DSUM(DataTable[#All],"Amount",$A$5:$C$6)

Result = $38,000 which represents the sum of the amount column in my table for Level 1 for the period January to March 2021.

Database Function Example 3 - Multiple AND OR Criteria

Ok, what if I wanted to SUM both Level 1 AND where dates are between January AND March, OR for Level 2 where dates are between January AND March?

Easy, add another line to your criteria table like this:

Remember to also update your DSUM formula to include the new row of criteria:

=DSUM(data_table,"Amount",$A$5:$C$7)

Result = $229,151 which is summing Level 1 and Level 2 amounts that are in the date range January – March 2021.

But wait, there’s more. Database functions aren’t limited to SUM. You can AVERAGE, COUNT, COUNTA, MAX, MIN and more.

They all use the same syntax, and they can all use the same criteria table.

=DCOUNT(data_table,"Amount",$A$5:$C$7)

Result: 15

=DAVERAGE(data_table,"Amount",$A$5:$C$7)

Result: $15,277

=DMAX(data_table,"Amount",$A$5:$C$7)

Result: $34,970

=DMIN(data_table,"Amount",$A$5:$C$7)

Result: $1000

You don’t even need to remember complicated acronyms for these functions. Just add a ‘D’ to the front of the function you want.

Database Function Example 4 - Multiple OR criteria, same field

The takeaway here is 'OR' criteria are listed down the rows, whereas 'AND' criteria are listed across the columns of a single row.

Database Function Example 5 - Multiple OR criteria, different field

This criteria will aggregate all data which is level 1, including level 1 for Atkins (because the name field is blank on the first criteria row), plus all other levels except those that are for Atkins (because the Type field is blank on the second criteria row). 

Database Function Example 6 - Wildcards

Database functions can also handle wildcards. Here I want to aggregate all data where the names begin with B.

Notice this time I used the column number in the field argument instead of the column name.

Database Function Example 7 - DGET Function

Unlike the other database functions which aggregate data, the DGET function only returns a single value. If your result returns more than one matching record you'll get the #NUM! error.

Excel Database Function Rules

1. Your criteria table only needs columns for the data you want to filter on. So, if your tabular data set has hundreds of columns, don’t panic. You don’t need every column replicated again in your criteria table.
2. You can add multiple 'OR' criteria by adding them to a new row in your criteria table.
3. If your Database formula includes criteria rows that are blank then it will sum/average/count etc. the whole table. Essentially ignoring your criteria altogether.
4. The criteria table can house formulas e.g. links to other cells, drop down lists and the like, so get creative with how you use them to incorporate interactivity into your reports.
5. The Criteria table is not case sensitive, not for the column labels or the criteria itself.
6. The ‘Field’ part of the formula can be the column name or the column number.

The Downside of Excel Database Functions

The obvious downside is that the Database Functions require a bit more worksheet real estate to house the criteria as opposed to an array formula and the like, but with over 1 million rows I think we can afford it.

I think the upsides, like the fact that you can see exactly what the criteria is in plain English and how easy they are to use far outweighs the downside.

Next Steps

If you’re finding these Excel functions useful, and you want to take your skills even further, check out my Advanced Excel Formulas course. It's packed with all the functions that will save you hours of work and help you stand out.

What’s great about this course is that it doesn’t just cover the basics—it dives into real-world examples with quick, bite-sized videos, showing you both the fundamentals and more advanced, less obvious uses of functions.

Once you’re done, you’ll have a solid understanding of how to create robust formulas that enable you to handle any scenario.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub

Want to sponsor our newsletters?  Just reply to this email to get in touch with us.