Part 1: DFunctions in Excel, what they are and how to use them.
The database functions perform a range of standard calculations to calculate statistics: sum, average, minimum, standard deviation etc. from any set of data and allow the user to specify any criteria.
This set of functions is a great tool when it gets to report on a specific set of data from a wide database. For example, if you want to know the number of people above a certain salary in a company, you can use DCOUNT, or if you want to know the average age of females subscribed to a club, you can use DAVERAGE.
All examples below are based on a sample Excel spreadsheet, click here to download it.
There are 12 database functions and they all work the same way. They are made of 3 arguments and follow this syntax: DFUNCTION(database,field,criteria).
- Database: It is the range of cells that makes up the data, including the headers. Note that the first row (header) should contain the labels of each column, as this will be used as a reference in the “field” section.
In our example, the database includes the whole section from A1 to E34 (syntax: A1:F34)
- Field: It contains the column you want to report on. It requires the column header entered in quotation marks
- Criteria: It is the range of cells that contains the condition(s) that is used to determine which values are calculated. The criteria can be anywhere in the spreadsheet, in any cell range as long as it at least contains one column header (what we called earlier a field) and a value underneath.
In our sample spreadsheet, we selected the range H2:H3 as the criteria to calculate the Average Salary only for people under 40 years old.
The criteria can be a combination of several conditions where all need to be met (we will call it “AND” criteria) or multiple sets of conditions where minimum 1 condition need to be met to count as the criteria (we can call it “OR” criteria).
Both “AND” and “OR” criteria are written differently:
- For an “OR” criteria, the conditions need to be listed in different rows (same column)
- For an “AND” criteria you will arrange the conditions across columns (same row).
For example, if we want to know the eldest person amongst Category A or C, we need to create an “OR” criteria as below. Note how the two conditions are listed in the same column but different rows.
However, if we are after the number of people amongst Category A that start in the company after 1st of January 2007, we will need to use an “AND” criteria and input the conditions on the same row but two different columns.
It is possible to combine both an “AND” and an “OR” criteria. For example, if we want to know the minimum salary for women above 30 years old OR men who are more than 40 years old, we have to create two lines for the criteria: one for Females >30 and one for Males >40.
I hope you have enjoyed this article.
Check out Part 2 of this tutorial where we are going into the detail of each function and give practical examples. Otherwise, if you have any questions about database functions, do not hesitate to leave a comment below or contact us at [email protected] or [email protected] .
You can also download our Database functions cheat sheet by entering your email address below! Keep this cheat sheet on your desk and always be reminded of how to use Excel’s powerful Database functions!
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018