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

## 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:

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:

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:

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.

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:

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:

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:

Here is what the formula looks like:

###### =DSUM(Tradie_DB, 3, criteria)

The answer is Plasterer.

## 9. Key Takeaways

**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!

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