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

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

About the author

Jason Morrell

Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.

He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.

Purely for amusement he sometimes talks about himself in the third person.

SHARE