Sign in or start a free trial to avail of this feature.
9. Database Functions
The relatively unknown database functions are great for quickly looking up multiple criteria. Learn how to use them in this lesson.
Resource - List of Useful Database Functions (00:00)
DSTDEV (Standard deviation for population sample)
DSTDEVP (Standard deviation for a population)
DVAR (Variance for a population sample)
DVARP (Variance for a population)
Using DSUM (00:03)
Database functions are a useful option if you want to perform single lookups with multiple criteria. The DSUM function can be used to perform a sum subject to several criteria. DSUM takes three arguments. First is the database, which is the full dataset, including the headers. Second is the field, which is the name of the field we want to sum. Third is the criteria, which is an array of cells containing column headers and the criteria we want to apply to them.
Here, we use DSUM to find the sales for an individual salesperson by entering their name in the criteria array.
Adding More Criteria (01:21)
The criteria array can contain as many columns as we want, with each column representing an individual criterion. A database function always applies all the criteria in the criteria array when performing its calculation.
In addition to DSUM, other database functions are available, such as DCOUNT. This function takes the same arguments as DSUM and counts all the instances of the field that meet the specified criteria. Here, we can find the number of transactions for a salesperson, instead of their value.
Database Function Requirements (02:43)
There are two requirements to be aware of when using database functions. First, the dataset must have headers in the top row, with the data arranged in rows underneath. Second, the headings in the criteria array must match the headings in the dataset, otherwise the functions won’t work.
In the previous lesson, we learned how to calculate monthly totals on our search panel. In this lesson, we'll learn how to use Excel's database functions. Database functions are a little-known set of commands in Excel that allow you to perform very powerful look-ups on your data sets. They're not as flexible as index match or Vlookup, but they are a great solution if you want to perform single look-ups on multiple criteria. There are 12 database functions in all, but the five most useful are listed in our search panel. Let's start off by looking at DSUM, to calculate the total revenue generated by the sales rep Baines. So first, I'll write the name Baines underneath Sales Person. And then we'll create our DSUM function. So write =DSUM.
And the first item we must enter is our database, which is our full data set including the headers. I have a name arranged for this called sales transactions, so I'll include that here.
Next, I need to include the field, and the field will be Revenue.
And lastly, I have to enter the criteria, which are these six cells.
I currently have three criteria, Revenue, Sales Person and Payment Date, but I can include as many criteria as I want. I'll then close the bracket, and press Enter to finish. And this tells me the total revenue contributed by Baines.
Now let's add some more criteria, starting with the payment date. So let's say we want to find out the revenue Baines contributed in the first half of the year. So let's say the payment date is less than the first of July, 2012.
And as you can see, the DSUM value updates accordingly.
Now let's add another criterion. Say we only want to include the transactions above $20 thousand in value. So let's say greater than 20 thousand, and again, you can see that the DSUM figure updates accordingly.
Let's now move on to DCOUNT. So I'll write =DCOUNT. Again I'm asked for the database, which is Sales Transactions.
The field can actually be any one of the headers, so I'll select the same one, and the criteria are the same six cells as before. I'll close the bracket, and press Enter to complete. And this tells me that Baines sold five transactions in the first half of the year above $20 thousand in value.
Hopefully it's becoming clear that the database functions are very powerful look-up tools, particularly if you have multiple criteria. Using the same inputs for DSUM and DCOUNT, you can also calculate DAVERAGE, DMAX and DMIN for your data set. I'm going to leave these three commands as an exercise for you to complete. Before I wrap up this lesson, it's important to mention a few requirements for using database functions. First, the data set in question must be arranged with the sales headers across the top, and the data arranged in rows underneath. Second, the headings in the fields must replicate exactly the spelling in the database headers, otherwise the functions won't work. However, once you have both of these requirements in place, the database functions are all yours to use.