How often do you work with tabular data sets? You know, like this one below where you’ve got column labels and then each row is a record:
When you work with data like this Database Functions are your best friend.
Why, because they’re so simple, yet they’ll do most things an array formula will do, only in English. So even if you don’t know your Binary from your Boolean you’ll be able to use them.
Who needs complicated Array formulas, SUMPRODUCT, INDEX MATCH etc. when you’ve got Database Functions? INDEX, SHMINDEX, I say. Well, not really but it sounded funny 
Database functions are so powerful, yet when you see how simple they are you’ll ask yourself ‘what,…. is that it’.
I know I’m going on about them, but they really are that good so do yourself a favour and keep reading.
Excel Database Function Example 1
Taking the data table below, let’s say I wanted to SUM all Level 1 invoices for the period January – March 2011.
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:
Then in another cell I type my database function for SUM, which is DSUM:
=DSUM(data_table,”Amount”,$A$4:$E$5)
Result = $38,000 which represents the sum of the amount column in my table that are for Level 1 for the period January to March 2011.
How the DSUM Works
DSUM syntax
=DSUM(database, field, criteria)
My DSUM formula:
=DSUM(data_table,"Amount",$A$4:$E$5)
-
database
= data_table. This is a named range for my tabular data set or ‘database’ if you’re referring to the syntax. Alternatively I could type in the ‘database’ range A8:E74 (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$4:$E$5. This is the range of my criteria table
Notes:
- 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.
- 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.
- I’ve left the criteria for the Name and Amount blank as I don’t want to filter on these columns, but if you wanted to filter for a particular Name you would just type the name in cell D5 and the DSUM would dynamically update.
Excel Database Function Example 2
Ok, what if I wanted to SUM both Level 1 and Level 2?
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$4:$E$6)
Result = $229,151 which is summing Level 1 and Level 2 amounts that are in the date range January – March 2011.
Excel Database Function Example 3
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$4:$E$6)
– result 15
=DAVERAGE(data_table,”Amount”,$A$4:$E$6)
– result $15,277
=DMAX(data_table,”Amount”,$A$4:$E$6)
– result $34,970
=DMIN(data_table,”Amount”,$A$4:$E$6)
– 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.
The Downside of Database Functions
Look, 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 I 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.
Excel Database Function Rules
- Your criteria table needs only 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.
- 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.
- 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.
- The Criteria table is not case sensitive, not for the column labels or the criteria itself.
- The ‘Field’ part of the formula can be the column name or the column number.
- When typing in the date criteria the actual cell contains =”<=31/03/2011″ but only <=31/03/2011 is visible. See example below.
This also applies to the amount column. Say you wanted to SUM amounts greater than $1000. In cells E5 and E6 you would type:
=”>1000”
Click here to download the workbook and play around with the different functions.
Did you like this? Let me know in your comments below, and share the knowledge; tell your friends on Facebook or Tweet about it.
Want more? Sign up for our newsletter below and get the bonus 100 Excel Tips & Tricks too.
