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