EXCEL DATABASE FUNCTION

Since their introduction in Excel 2007, DataBase functions have remained Overlooked & Underutilised.

This is an Introduction in a  Series of articles whose aim is to demystify these Database functions.

Database functions include; DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DGET, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVAR.

All these functions works basically the same way and have the same syntax  i.e Performs specific calculations on a specified field whose records meet specified criteria.  NB: You can replace DSUM below with any of the other database functions.

Syntax = DSUM(Database, Field, Criteria)

Database ► Where all records are inclusive of header rows. Your data should be structured in a way that Each Row is a Record, Each Column is a Field  & Top rows contain headers that identify the fields

Field ► A column that holds ONE particular item of data eg Order dates or Order ID

Criteria ► A  section in the Worksheet (apart from the database) which holds Criteria.

Notes:

• Always use  a named range (Orders) instead of Cell Address (A1:G8400) for your database area.

• Establish a Criteria Area that should be structure in such a way that for every Field name there is a cell below it where you enter the criterion to be met.

NB

1. You can use a named range or cell address on your criteria Area
2. The Criteria area can contain a Single Criterion or Multiple criteria
3. The Criteria area MUST NOT include any Blank rows or Columns
4. To find records that MUST meet two or more criteria, place the values in adjacent columns e.g above image will only consider Order Qtys that are above 11 AND customer name is Dorothy Badders and Ship code Starts with KBX
5. To find records that meet ANY one of the Several criteria, place the criteria in two or more rows below the field name. For example  below criteria will only consider Order Qty above 30 AND customer Don Miller AND All orders whose ship code starts with KKK

NB. For more on how to set up COMPLEX CRITERIA

ADVANTAGES OF USING DATABASE FUNCTIONS

1. Fastest calculations
2. Great for Complex criteria
3. Works  well in large spreadSheets

DISADVANTAGES OF USING DATABASE FUNCTIONS

1. They have a limited use i.e. Perform basic calculations for a specified subset of the records in your database.

RELATED ARTICLES

EXCEL DSUM FUNCTION

DAVERAGE VS AVERAGEIF & DCOUNT VS COUNTIF

RECOMMENDED ARTICLES

Excel Is Fun Video

Database functions (reference)