Excel Database Functions - BETTER than SUMIFS, COUNTIFS etc.!

database functions have been around for donkey's years so it's surprising that not many people know about them there's a database function for nearly every aggregation type from average count and sum right through to variance and they're a great alternative to the ifs functions like sumifs average ifs minifs etc except they're more powerful let's take a look database functions require your data to be in a tabular format which makes excel tables ideal here's my example data and you can see it's in a table named data table it took me ages to think of that name now it's a list of invoices by type date invoice number name and amount now the nice thing about database functions is the syntax is the same for each variation the first argument is a reference to the database which sounds complicated but really it's just the range of cells or table containing your data the next argument is the name or number of the field that you want to aggregate in these examples i'm going to aggregate the amount column and lastly the criteria which are stored in a table of their own consisting of the column headers that you want to use and then the criteria in this example my criteria are type level 1 and dates between january 1st and march 31st 2021. let's look at a formula example i can use dsum to sum the amounts for level one between january and march my database is the table so i want to select not just the table body but also the headers and that puts hash all after the table name then i enter the name of the field in the table that i want to sum in double quotes because this is text and then lastly i select my criteria and the headings for my criteria table now i don't need to select the name and amount columns they don't have any criteria so i'm just going to select the date columns and the type column i'll press f4 to absolute that reference so that i can copy this formula across close parentheses on d sum and there's my result now i can copy this across because the syntax is the same and then all i need to do is change the function being used so this one is d count and then this one is d average and then we have d max and d min now you notice there are some other database functions if we type in d you can see there's d count a so that's going to count both numbers and text we've also got d get which we'll look at later d maximin which we've already looked at d standard deviation and the standard deviation of a population d sum which we looked at now and d var and d var p so they're variances and variance of a population and again the syntax is the same for all of them now in these examples we're looking at and criteria that is where records are level 1 and dates are greater than or equal to january 1 and less than or equal to march 31st we can already aggregate values using and criteria with functions like sumifs countifs and average ifs however with the database functions we can also handle all criteria and even a mix of and with or let's look at some examples let's say we want to sum the amounts for level one from january to march or level two from january to march all i need to do is type in another criteria for level two and then i'll copy this criteria down for the dates and then what i need to do is edit this range that's been referenced to include the next criteria row now it's important here that you don't include blank rows in your criteria argument because that's basically going to say to excel sum everything so in this case i have two rows of criteria so i've selected them in my reference for the criteria argument and now you can see the d sum is returning two hundred and twenty nine thousand one fifty one i won't waste time updating these you get the idea the next example i want to look at is multiple or criteria for the same field it's super easy let's say i want to sum level 1 or level 2 and i don't care about the dates so i can simply delete the date criteria it doesn't matter that dsum still references the date columns the fact that they're empty will just tell excel to sum all dates where the type is level one or level two now in specifying multiple or criteria for different fields it can be a little mind-bending so let's look at an example here i want to sum type level one and i want a sum where the name is not equal to atkins i need to make sure my reference includes the name column so here the dsum function is aggregating all data which is level 1 including level 1 for all names because the name fields blank on the first criteria row plus all other levels that are not for atkins because in this case the type field is blank on the row that contains the atkins criteria so you can see it can get a little bit confusing when you're using multiple or criteria across different fields now database functions can also handle wildcards so for example let's say we want to sum when names begin with b so i'll get rid of the type there and in here i'm going to type in b and the asterisk so it's just going to sum when names begin with b i need to remove the blank row from the criteria so that it doesn't incorrectly sum everything and now we have the sum where the names begin with b now another way we can reference the field is with a number so instead of typing in amount i could simply enter the number of the column in this case amount is the fifth column in the table so i type in a five you can see i get the same result but instead of typing in the word amount i simply type in the number five and notice it's not in double quotes it's a number it isn't text so it doesn't need the double quotes unlike the other database functions which aggregate data the dgap function is the old one out because it only returns a single value a bit like vlookup or x lookup and that means if your result returns more than one matching record you'll get the hash num error let's look at an example so equals d get we're going to reference the table here the field we want to return is number five column number five for the amount and the criteria well let's just say we want it to find the amount for invoice number 603 so it returns 4 000 perfect however if we were to ask it to return say type level 1 we'll just change that reference to there we get the num error and that's because there are multiple results for level one so it can't return a single value it's going to give you the error to wrap up i just want to quickly cover the rules for database functions so firstly keep in mind that your criteria table only needs columns for the data that 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 on your criteria table be careful though only to reference the rows in the criteria table that aren't blank if your database formula includes criteria rows that are blank it's simply going to sum or average or count the whole table essentially ignoring your criteria altogether the criteria table can house formulas for example links to other cells drop down lists and the likes so get creative with how you use them to incorporate interactivity into your reports the criteria is not case sensitive not for the column labels or the criteria itself and lastly remember the field argument of the formula can be the column name or the column number i hope you're keen to give database functions a try you can download the file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 33,991

Rating: 5 out of 5

Keywords: excel database functions, dsum

Id: rbDHQm6CDRc

Length: 9min 4sec (544 seconds)

Published: Thu Sep 23 2021