By utilizing the DSUM function we can add criteria to the sum we’re going to say hey excel. I want to sum up the total sales but I only want to do it where the category is equal to rents, or where the categories equal to telemarketing or whatever it happens to be whatever our criteria is for our SUM, let’s take a look at the first one here. There’s actually a few different types of database functions DSUM just happens to be one of them. So let’s take a look at working with DSUM now in order for the DSUM to work, our first step is we have to set up the criteria section of the function we essentially need to identify which column we want to search for a value.
For example, we’re say Hey Excel I want you to go to the category column and I want you to find rent and then give me the sum of that record.
I’m going to put in a couple little pieces of information that’s going to help assist with this function.
The first thing, I’m going to put in here and I’m going to start this inside of it’s actually start the first thing I’m going to put in here and I’m going to start this inside of it’s actually start, this is important because that text straight there matches this text right here has to be spelled the same way, no extra spaces in there right if I go into that cell go to the formula bar it’s just straight up category. There’s nothing else in there and it’s spelled just like it is here inside of the list. That’s very important because that’s how Excel is going to identify the column that we want to search for our criteria with that now right below that’s in the next adjacent cell down.
I’m going to put in the criteria that I wanted to search for, in this case I’m just type in rent.
We’ve got the setup to use one of our database functions.
In this case the DSUM function, we’ve got our lists just there on the left.
I want you to sum up this column where this is true category equals rent.
The DSUM has three arguments three pieces of information that it needs from us in order for it to give us the total sales based on the criteria.
Now to make this a little bit easier to read once I got that equals DSUM open parentheses, I’m going to go quick on the Fx button, this should open up my argument window or if you’re on a Mac you know open up the formula builder panel on the right hand side for you
The three options that I have their database.
First thing the function needs to know is where’s your list.
What’s the range for your list.
I’m going to go select a one that’s the top of my list, I’m going to use some shortcut keys here, I’m going to do Ctrl + Shift + down arrow, that’ll highlight that column all the way down to the bottom. Then I’m going to do Ctrl + Shift + right arrow and that will highlight all of the data all the columns. I’ve got A1 to F59.
If you’re shortcut keys aren’t working for you, I love them but sometimes they don’t work, your keyboard set up is a little bit different, You can just type in the A1:F59 or you can click and drag to highlight that range as well. The important part is we get the database the whole list inside of that field.
The next thing it needs to know is the field that we ultimately want to SUM, in this case we want to sum the total sales column because we already selected the entire list.
The only thing the field needs is F1 that cell right there, total sales F1 where that’s not that total sales cell lives you can see down here field is either the label of the column, I just click on the cell and done.
The next thing that we need inside there is the criteria so we specified the database the lists. We’ve told that which column we want to sum and then the criteria is going to be this area.
Here are i2:i3 range that includes our header and the adjacent cell of i3 which contains your rent.
We don’t have the filter list there’s nothing else additional that we had to do list. then hit OK, and we get our total results our total sales based on the category of rent.