Excel Database functions have been around for donkey's years so it's surprising that not many people know about them. They work great with tabular data and Excel Tables. And they're easier to use and more powerful than the newer, more popular equivalent functions like SUMIFS, COUNTIFS etc. Show
Watch the VideoDownload the WorkbookEnter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we can email you our Excel newsletter. Please enter a valid email address. Click here to download the workbook and play around with the different functions. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download. Excel Database FunctionsThere is a database function for nearly every aggregation type, from average through to variance, as listed below. They're an alternative to SUMIFS, AVERAGEIFS, MINIFS etc. except more powerful. FunctionDescriptionDAVERAGE functionReturns the average of selected database entriesDCOUNT functionCounts the cells that contain numbers in a databaseDCOUNTA functionCounts nonblank cells in a databaseDGET functionExtracts from a database a single record that matches the specified criteriaDMAX functionReturns the maximum value from selected database entriesDMIN functionReturns the minimum value from selected database entriesDPRODUCT functionMultiplies the values in a particular field of records that match the criteria in a databaseDSTDEV functionEstimates the standard deviation based on a sample of selected database entriesDSTDEVP functionCalculates the standard deviation based on the entire population of selected database entriesDSUM functionAdds the numbers in the field column of records in the database that match the criteriaDVAR functionEstimates variance based on a sample from selected database entriesDVARP functionCalculates variance based on the entire population of selected database entriesDatabase Function Example 1 - Multiple AND CriteriaTaking the data table below, let’s say I wanted to SUM all Level 1 invoices for the period January – March 2021. 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: Note: my dates are dd/mm/yyyy. Please change the dates in the criteria table to suit your Excel region settings. Then in another cell I type my database function for SUM, which is DSUM: =DSUM(DataTable[#All],"Amount",$A$5:$C$6) Result = $38,000 which represents the sum of the amount column in my table for Level 1 for the period January to March 2021. How DSUM WorksDSUM syntax =DSUM(database, field, criteria) My DSUM formula: =DSUM(DataTable[#All],"Amount",$A$5:$C$6)
Database Function Example 2 - Multiple AND OR CriteriaOk, what if I wanted to SUM both Level 1 AND for dates between January AND March, OR for Level 2 for dates between January AND March? 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$5:$C$7) Result = $229,151 which is summing Level 1 and Level 2 amounts that are in the date range January – March 2021. 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$5:$C$7) – result 15 =DAVERAGE(data_table,"Amount",$A$5:$C$7) – result $15,277 =DMAX(data_table,"Amount",$A$5:$C$7) – result $34,970 =DSUM(database, field, criteria)0 – 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. Database Function Example 3 - Multiple OR criteria, same fieldDatabase Function Example 4 - Multiple OR criteria, different fieldThis criteria will aggregate all data which is level 1, including level 1 for Atkins (because the name field is blank on the first criteria row), plus all other levels except those that are for Atkins (because the Type field is blank on the second criteria row). Caution: it will double count Level 1 <> Atkins and this is probably not what you want. Database Function Example 5 - WildcardsDGET Function #NUM! errorUnlike the other database functions which aggregate data, the DGET function only returns a single value. If your result returns more than one matching record you'll get the #NUM! error. Excel Database Function Rules
The Downside of Excel Database FunctionsThe 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 we 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. How do you use Excel if function with multiple conditions?Another way to get an Excel IF to test multiple conditions is by using an array formula. To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.
Can the DSUM function include multiple criteria?Using the multi-row criteria
It is important to know that the DSUM function can include more than one row below the headers. In this case, each row needs to be joined by the OR logic. Also, the expressions in the given criteria will be joined by the AND logic.
Can you do 3 IFS in Excel?Excel allows a max of 7 nested if statements. If we wanted to expand our list of possible statuses, we could add only one more condition and one more status. But fortunately we can add more using a different function.
What is Dmax in Excel?Description. Returns the largest number in a field (column) of records in a list or database that matches conditions you that specify.
|