Database Functions: 5 Easy Old-School Reporting Functions

AUTHOR:  JASON MORRELL 

Jason Morrell

Database functions summarise the data within a table (or database). Their functionality has mostly been superseded by pivot tables and combo functions (COUNTIF, SUMIF etc.) but there are still some situations where you can use them. 

1. What are database functions?

Worksheet functions (e.g. SUM, COUNT etc.) work on the whole list, regardless of whether or not it has been filtered.

Database functions are able to work on a filtered list. Examples of database functions are DSUM, DCOUNT, DMAX, DMIN, DAVERAGE and DGET.

Database functions requires three pieces of information:

  • The source table (database) to be analysed.
  • The column number within the database that you want to do something with (e.g. SUM).
  • The criteria table which contains one or more criteria that are used to match relevant rows on the source table.

The structure for database function formulas is:

Where column headings from the source table and the criteria table match and there is a criteria provided, it will be used to filter the source table and perform the required action on that data.

=databasefunction (source_table, col_no, criteria_table)

2. A sample tradie database

Here is a sample tradie database that I will use to demonstrate a variety of database functions below. The table is a formatted table and has been named Tradie_DB.

Tradie database

3. DSUM

Q) How many jobs were assigned to the plasterers?

DSUM totals up the values in a specified column of a table that meets certain criteria.

Here is the criteria table, which is named criteria for convenience:

Database functions criteria table - Plasterer

The DSUM function will look at the Trade column of the source table and pick out every 'Plasterer'.

It will then SUM the corresponding values in the Assigned Jobs column (column #5).

Here is what the formula looks like:

=DSUM(Tradie_DB, 5, criteria)

The answer is 454.

4. DCOUNT

Q) How many Holdens are driven by Plumbers?

DCOUNT counts how many values in a specified column of a table meets a certain criteria.

Here is the criteria table, which is named criteria for convenience:

Database functions criteria table - Holden Plumber

The COUNT function will look at the Car and Trade column of the source table and pick out every row where the car is 'Holden' and the trade is 'Plasterer'.

Because COUNT only works with numeric data, we can provide any column number that contains numbers. In this example, columns 4 or 5 could be used.

Here is what the formula looks like:

=DCOUNT(Tradie_DB, 5, criteria)

The answer is 5.

5. DAVERAGE

Q) What is the average number of jobs assigned to brickies?

DAVERAGE averages the values in a specified column of a table where certain criteria are met.

Here is the criteria table, which is named criteria for convenience:

Database functions criteria table - Brickie

The DAVERAGE function will look at the Trade column of the source table and pick out every 'Brickie'.

It will then AVERAGE the corresponding values in the Assigned Jobs column (column #5).

Here is what the formula looks like:

=DAVERAGE(Tradie_DB, 5, criteria)

The answer is 26.6.

6. DMAX

Q) What is the largest number of jobs assigned?

DMAX finds the largest value in a specified column of a table that meets certain criteria.

Here is the criteria table, which is named criteria for convenience. It is blank because we are looking for largest figure for ALL tradies.

Database functions criteria table - Empty

The DMAX function will look at the whole source table.

It will then pick out the largest value in the Assigned Jobs column (column #5).

Here is what the formula looks like:

=DMAX(Tradie_DB, 5, criteria)

The answer is 104.

7. DMIN

Q) What is the cheapest hourly rate?

DMIN finds the smallest value in a specified column of a table that meets certain criteria.

Here is the criteria table, which is named criteria for convenience:

Database functions criteria table - Empty

The DMIN function will look at the whole source table.

It will then select the smallest value in the Hourly Rate column (column #4).

Here is what the formula looks like:

=DSUM(Tradie_DB, 4, criteria)

The answer is 80.

8.. DGET

Q) What car does Dale drive?

DGET retrieves a value from a specified column of a table that meets certain criteria.

Here is the criteria table, which is named criteria for convenience:

Database functions criteria table - Dale

The DGET function will look at the Name column of the source table and find 'Dale'.

It will then retrieve the corresponding values from the Car column (column #2).

Here is what the formula looks like:

=DSUM(Tradie_DB, 2, criteria)

The answer is Ford.

Q) What is Sharktooth's trade?

Here is the criteria table, which is named criteria for convenience:

Database functions criteria table - Sharktooth

Here is what the formula looks like:

=DSUM(Tradie_DB, 3, criteria)

The answer is Plasterer.

9. Key Takeaways

  • DSUM counts how many cells in one column of a table meet specified criteria.
  • DMAX finds the largest value in one column of a table that meets specified criteria.
  • DMIN finds the smallest value in one column of a table that meets specified criteria.
  • DAVERAGE averages the cells in one column of a table that meet specified criteria.
  • DGET returns the contents of a cell in one column of a table that meets the specified criteria. The criteria must refer to data that is unique.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
10 Steps to a Perfect Print Page Setup in Excel With Auto-Repeat Headings and Controlled Page Breaks
Read More
Excel Auditing Checklist: 2 Auto Features and 4 Tools to Quickly Identify and Rectify Errors BEFORE You Publish
Read More
Supercharge Your Cut and Paste: Popular Methods, Shortcuts and 2 Smarter Approaches
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Office Mastery
>