Excel Tip of the Week #288 - Revisiting database functions

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a Creator level post in which we are looking again at the family of "database" functions in Excel, last covered in TOTW #154.  This Tip builds directly on last week's Tip about Advanced Filter, so you might want to revise that, but as a quick reminder, Advanced Filter requires us to use a special layout to apply complex filters in Excel:

We're going to be building on the same example as last time - you can download the file from the bottom of this post.

What are database functions?

Essentially, the database functions are functions that work a lot like other common Excel functions, but with the added element that they can filter which data they work with using an Advanced Filter-like layout.  This is like having access to a bunch more SUMIF-like functions - except database functions are actually a bit more flexible when it comes to either-or conditions, which are not supported by traditional functions like SUMIF that instead require all of their conditions be met.

Here is the full list of database functions, along with their regular-function equivalents:

Database function Syntax Standard function equivalent
DCOUNT =DCOUNT(database, field, criteria) COUNTIF/S
DCOUNTA =DCOUNTA(database, field, criteria) COUNTA with IF (does not exist)
DGET =DGET(database, field, criteria) VLOOKUP with IF (does not exist)
DMAX =DMAX(database, field, criteria) MAX with IF (later Excel versions only)
DMIN =DMIN(database, field, criteria) MIN with IF (later Excel versions only)
DPRODUCT =DPRODUCT(database, field, criteria) PRODUCT with IF (does not exist)
DSTDEV =DSTDEV(database, field, criteria) STDEV with IF (does not exist)
DSTDEVP =DSTDEVP(database, field, criteria) STDEV.P with IF (does not exist)
DSUM =DSUM(database, field, criteria) SUMIF/S
DVAR =DVAR(database, field, criteria) VAR with IF (does not exist)
DVARP =DVARP(database, field, criteria) VAR.P with IF (does not exist)

As you can see, the functions are generally named with a D and then the name of a normal function.

All of these functions are written the same way, with the same three inputs:

Database is the range containing the database (table) in question - including headers
Field is the name of the particular field (column) that the function will be looking at - this can either be entered as text between quotation marks, or a cell reference to the field name.
Criteria is a range with the criteria in it.  As discussed in last week's Tip on Advanced Filter, this has to consist of column headings with conditions written underneath them.

Let's look at an example - a function that will find the value of the largest invoice approved by Haran:

Going further

We discussed earlier that we can make more flexible conditions with these functions than with traditional ones - let's look at an example of that.

When writing more complex conditions, the basic rule is that rules on the same row are additive (both must be satisfied), whereas rules on different rows are optional (either could be satisfied).  You can create multiple conditions for the same column by duplicating the appropriate column heading.

So, here's a compound condition and formula for "what was the average value of a Riemann Brothers invoice approved by either Gauss or Euler?".  Note that there is no DAVERAGE function, but we can use DSUM/DCOUNT to get to the same result.

Download the file from below and see what kinds of functions and conditions you can make!

Previous post - Revisiting Advanced Filter
TOTW index 
Next post - Floating point errors

Anonymous