1. Summarizing data with database functions
Time for the database functions in practice!
In the worksheet we’ve got an invoice database table, the criteria table and the summary table, where we will perform the calculations.
Let’s start by summing the sales using the DSUM function. We type DSUM, then we need to select the database, which is the entire table. We can also make it easier by applying the Named range to the table, let’s do that first.
Back to our function: DSUM, then Database, then the reference to the field we want to summarize, so J14, and then finally the criteria table. Note that the criteria table is blank for now, so without any conditions, the computed value will represent the total sales.
Quick check, it’s indeed the same number!
Remember that we can replace the cell reference of Sales with a column number, in this case, column 9.
Or even hardcode it to “Sales”.
Let’s type in some criteria. Suppose we want to summarize a specific invoice. Let’s pick this one.
Let’s copy its number in the criteria under InvoiceNo column. Note that the criteria field names must be the same as in our database.
Quick check. This worked! Let’s convert it into a handy selection tool.
We empty this cell and go to Data and Data validation. We will select to allow List and source the invoice numbers.
OK and we have a handy drop down menu!
Let’s erase the InvoiceNo from the criteria table and add more conditions. Suppose we’d like to sum the sales where unit price is > $ 10. We type in >10 here. Easy!
To add AND <20 condition, we just add an extra column UnitPrice to the right and type in <20.
What if we would like to introduce OR condition, say OR Sales with unit price >100?
We need to add a row to the criteria table and input it there.
Nothing happened yet! We still didn’t include that extra criteria row in our calculation. Let’s get back to DSUM function and do that.
It works!
Let’s also filter on the United Kingdom in the first condition.
Feeling lazy? We don’t even need to input the entire Country name. How about typing U with a question mark? It will provide sales for countries starting with letter U.
Oh Oh.. we forgot there are more such countries in out database:
Let’s replace it with *Kingdom*, between asterisks. It will find word Kingdom within the possible countries.
Once we are happy with our criteria, it’s a good idea to give it a Named range “Criteria”. We will adjust our DSUM function’s last argument to reflect that.
Now, let’s drag the calculation right and change the function name to DAVERAGE. And we also have an average of such bespoke selection of sales!
So easy!
Now onto you! It’s time to do some exercises on a similar data set to this one!
2. Let's practice!