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 easier to use and more powerful than the newer, more popular equivalent functions like SUMIFS, COUNTIFS etc.

Watch the Video

Download the Workbook

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 - Multiple AND Criteria

Taking the data table below, let’s say I wanted to SUM all Level 1 invoices for the period January – March 2021.

The first thing I need to do is set up a table that will hold my criteria (this can go on any worksheet in your workbook), like this:

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

Then in another cell I type my database function for SUM, which is DSUM:

=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.

How DSUM Works

DSUM syntax

=DSUM(database, field, criteria)

My DSUM formula:

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

• 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 A9:E75 (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

  = $A$5:$C$6. This is the range of my criteria table

1. The column labels (used in the 'field') in my criteria are the same as the column labels in my tabular data set. You must do this for the Database functions to work.
2. I’ve got two columns for the Date so that I can specify a range. If you wanted to only SUM one day then you would need only one column in your criteria table.
3. I've not included criteria for the Name and Amount as I don’t want to filter on these columns. However, if you wanted to filter for a particular Name you would just type the name in cell D5 and DSUM would dynamically update.

Database Function Example 2 - Multiple AND OR Criteria

Ok, what if I wanted to SUM both Level 1 AND for dates between January AND March, OR for Level 2 for dates 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 3 - Multiple OR criteria, same field

Database Function Example 4 - 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). Caution: it will double count Level 1 <> Atkins and this is probably not what you want.

Database Function Example 5 - Wildcards

DGET Function #NUM! error

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 criteria by adding them to a new row in your criteria table. Be careful to update your Database formula to incorporate new criteria and also be careful when you delete whole criteria rows. 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.
3. 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.
4. The Criteria table is not case sensitive, not for the column labels or the criteria itself.
5. 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.