• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Easy Excel Database Functions

You are here: Home / Excel Formulas / Easy Excel Database Functions
Easy Excel Database Functions
September 7, 2011 by Mynda Treacy

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.

Watch the Video

Subscribe YouTube

Download the Workbook

Enter your email address below to download the sample 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 Functions

There 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.

Function Description
DAVERAGE function Returns the average of selected database entries
DCOUNT function Counts the cells that contain numbers in a database
DCOUNTA function Counts nonblank cells in a database
DGET function Extracts from a database a single record that matches the specified criteria
DMAX function Returns the maximum value from selected database entries
DMIN function Returns the minimum value from selected database entries
DPRODUCT function Multiplies the values in a particular field of records that match the criteria in a database
DSTDEV function Estimates the standard deviation based on a sample of selected database entries
DSTDEVP function Calculates the standard deviation based on the entire population of selected database entries
DSUM function Adds the numbers in the field column of records in the database that match the criteria
DVAR function Estimates variance based on a sample from selected database entries
DVARP function Calculates variance based on the entire population of selected database entries

Database Function Example 1 - Multiple AND Criteria

Taking the data table below, let’s say I wanted to SUM all Level 1 invoices for the period January – March 2021.

excel dsum

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:

dsum

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 Works

DSUM syntax

=DSUM(database, field, criteria)

My DSUM formula:

=DSUM(DataTable[#All],"Amount",$A$5:$C$6)
  • database
    = DataTable[#All]. This is a Table Structured Reference for my tabular data set including the column labels, hence [#ALL] in the reference. Alternatively I could type in the cell range A9:E75 (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$5:$C$6. This is the range of my criteria table
Notes:
  1. 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.
  2. 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.
  3. I've not included criteria for the Name and Amount as I don’t want to filter on these columns. However, if you wanted to filter for a particular Name you would just type the name in cell D5 and DSUM would dynamically update.

Database Function Example 2 - Multiple AND OR Criteria

Ok, 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:

excel database function

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

=DMIN(data_table,"Amount",$A$5:$C$7)

– 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 field

excel database functions multiple or criteria

Database Function Example 4 - Multiple OR criteria, different field

excel database function different field or criteria

This 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 - Wildcards

excel database function wildcards

DGET Function #NUM! error

Unlike 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.

DGET function

Excel Database Function Rules

  1. Your criteria table only needs 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.
  2. 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.
  3. 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.
  4. The Criteria table is not case sensitive, not for the column labels or the criteria itself.
  5. The ‘Field’ part of the formula can be the column name or the column number.

The Downside of Excel Database Functions

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 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.

Easy Excel Database Functions

More Database Posts

Create a Pivot Table Direct From Access

Create a Pivot Table Direct From Access

Importing Data into Excel

Importing Data into Excel

More Excel Formulas Posts

ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel FormulasTag: Database
Previous Post:Excel Wildcards in your SUMIF, COUNTIF and VLOOKUPExcel Wildcards in your SUMIF, COUNTIF and VLOOKUP
Next Post:Excel Paste ShortcutExcel Paste Shortcut

Reader Interactions

Comments

  1. Matt

    November 11, 2021 at 7:23 am

    This is really neat.

    Is there a way to nest with sumproduct? It would be great if i could do it for all my expense categories on a P&L.

    Thanks!
    Matt

    Reply
    • Mynda Treacy

      November 11, 2021 at 6:56 pm

      Not sure why you’d need to nest it with SUMPRODUCT. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  2. imran

    September 26, 2021 at 3:21 am

    nice thanks’

    Reply
    • Mynda Treacy

      September 27, 2021 at 9:54 am

      Glad you liked it, Imran!

      Reply
  3. irfan

    September 23, 2021 at 11:28 pm

    Very nice

    Reply
    • Mynda Treacy

      September 24, 2021 at 10:11 am

      Thanks, Irfan!

      Reply
  4. Javed

    November 30, 2017 at 5:53 am

    Good

    Reply
  5. Kamala

    January 30, 2013 at 5:50 pm

    Hi,

    Its really a useful tips. Just want to know if I want to multy two coloumns then what needs to be done

    Thanks

    Reply
    • Carlo Estopia

      January 30, 2013 at 7:08 pm

      Hi Kamala,

      I don’t think database functions are appropriate of multiplying two columns simply because DPRODUCT cannot accept two fields or more as arguments.

      You can use 2 DGET FUNCTIONS though just to work it around but then if you have plenty of record that meets the criteria then you’ll also get
      an error.

      Please send your further clarifications via HelpDesk because I see that Database Functions is not what you really wanted here.

      Sincerely,

      CarloE

      Reply
  6. Bob

    January 23, 2013 at 2:47 pm

    Hi Mynda,
    Using the above table, how would I go about listing the entire table greater than 1000?
    This way level 7 would not be included and the list would look something like
    Level 1 $38,000
    Level 2 $26,154
    Level 3 $……..
    Level 4 $……..
    Level 5 $……..
    Level 6 $…….
    Level 8 $……..

    Then to add some spice, if we had the same table on multiple sheets and wanted the list to SUM all the tables as above into the one list? Meaning the one list contained the results of the SUM of the different tables on multiple sheets.

    Reply
    • Carlo Estopia

      January 23, 2013 at 6:01 pm

      Hi Bob,

      I would greatly appreciate if you would send a file through Help Desk containing all the data and a short explanation of your desired results.

      I could think of using a Pivot Table right now but I need your file.

      Sincerely,

      CarloE

      Reply
  7. Jamil

    December 6, 2012 at 3:17 am

    Dear Mynda,

    thanks for this. very useful. but the workbook you uploaded once i click on the sheet Database Functions G5 and press enter then the amount that was previously shown as result of DSUM formula disappears.

    would appreciate your feedback on this issue.

    thanks.

    Jamil

    Reply
    • Mynda Treacy

      December 6, 2012 at 8:21 pm

      Hi Jamil,

      I’m not sure why this would be happening. The only thing I can think of is that you haven’t clicked on the ‘enable workbook’ button above the formula bar when you first open the workbook.

      Kind regards,

      Mynda.

      Reply
    • Mohamed Bakr

      October 21, 2021 at 12:52 pm

      Hi Jamil,

      I believe it’s been a while and you might have found your find your answer already. I’m just putting this here for anyone else trying to find the answer to the same question.

      If your system or Excel dates are formatted as “mm/dd/yyyy” you need to change the date criteria in the cells I7, I16, and I17 to <=03/31/2021 instead of <=31/03/2021 to match your date formating.

      This should take care of the problem.

      Regards,
      M.Bakr

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.