How to Use Database Functions in Excel (With Examples)

This article illustrates how to use the database functions in Excel. There are 12 database functions in Excel. This article shows how to apply them one by one. The following picture highlights the purpose of this article. Have a quick look through the article to learn to use the rest of them.

Introduction to the Database Functions in Excel

The database functions in Excel allow us to perform simple operations like sum, product, average, etc on a particular database. There are 12 database functions in Excel. They are the DSUM, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, and DVARP functions. The names of each of the functions start with the letter D which stands for Database.

Each database function contains the same arguments. For example, the DSUM function has the following arguments.

Syntax:

Arguments:

database: The list or range of data on which you want to perform the operations. You can use cell references or named ranges to refer to the database.

field: Refers to the column in the database from which the results will be returned. You can either type the column header inside double quotes, give the column number within the database, or enter the cell reference of the column header in the formula.

criteria: This is the range that contains the specified conditions. The function will pull data only from the cells that satisfy these criteria. You can add multiple criteria using >, <, =, <>, etc.

How to Use 12 Database Functions in Excel

We will use the following dataset to illustrate how to use the database functions in Excel.

1. The DSUM Function

The DSUM function adds the numbers in the field (column) of records in the database that match the specified condition. Follow the steps below to learn how to use this function in Excel.

📌 Steps

• First, you need to create a criteria range before trying to use the database functions. We will create one and use it for all of the functions.
• Now, enter Tree, Height, Age, Yield, Profit, and Height in cells B12 to G12 You can add more headers besides them to specify more conditions.
• Then, enter =“=Apple”, >10 and <16 in cells B13, C13, and G13 These criteria indicate apple trees with heights between 10 and 16.
• You can add more criteria in the rows below. For example, enter =“Pear” in cell B14 to include the pear trees in the criteria.
• Next, enter the following formula in cell G17. This will return the sum of profits from the apple trees only because of the criteria specified in B12:B13.

• After that, enter the following formula in cell G18 to get the sum of the profits from all of the pear trees and only the apple trees with heights between 10 and 16.

2. The DAVERAGE Function

The DAVERAGE function averages the values in a column (field) in a list or database that match the specified conditions.

Now, enter the following formula in cell G17 to get the average yields of the apple trees whose heights are more than 10 feet.

Then, enter the following formula in cell G18 instead to get the average yields of all trees. Here, 3 refers to the third column (Yield) in the database.

3. The DCOUNT Function

The DCOUNT function counts the cells containing numbers in the field (column) of records in the database that match the specified conditions.

Now, enter the following formula in cell G17 to count the cells in the Age column that contain numbers and correspond to the apple trees with heights between 10 and 16.

4. The DCOUNTA Function

The DCOUNTA function counts nonblank cells in the field (column) of records in the database that match the specified conditions.

Now, enter the following formula in cell G17 to count the nonblank cells in the Profit column that corresponds to apple trees with heights between 10 and 16.

5. The DGET Function

The DGET function extracts from a database a single record that matches the specified conditions. It returns results similar to the results returned by the lookup functions.

Now, enter the following formula in cell G17 to get the yields for the apple and pear trees. The formula returns the #NUM error if it finds multiple values satisfying the specified conditions.

After that, enter the following formula in cell G18. Then it will not show any error because this time only one result meets the specified criteria.

6. The DMAX Function

The DMAX function returns the largest number in the field (column) of records in the database that matches the specified conditions.

Now, enter the following formula in cell G17 to get the maximum profit from all pear trees and apple trees with heights between 10 and 16.

7. The DMIN Function

The DMIN function returns the smallest number in the field (column) of records in the database that matches the specified conditions.

Now, enter the following formula in cell G17 to get the minimum profit from all pear trees and the apple trees with heights between 10 and 16.

8. The DPRODUCT Function

The DPRODUCT function multiplies the values in the field (column) of records in the database that match the specified conditions.

Now, enter the following formula in cell G17 to get the product of yields from all pear trees and the apple trees with heights between 10 and 16.

9. The DSTDEV Function

The DSTDEV function estimates the standard deviation based on a sample from selected database entries.

Now, enter the following formula in cell G17 to get the standard deviation of yields from all pear trees and the apple trees with heights between 10 and 16 based on a sample from selected database entries.

10. The DSTDEVP Function

The DSTDEVP function calculates the standard deviation based on the entire population of selected database entries.

Now, enter the following formula in cell G17 to get the standard deviation of yields from all pear trees and the apple trees with heights between 10 and 16 based on the entire population of selected database entries.

11. The DVAR Function

The DVAR function estimates variance based on a sample from selected database entries.

Now, enter the following formula in cell G17 to get the variance of yields from all pear trees and the apple trees with heights between 10 and 16 based on a sample from selected database entries.

12. The DVARP Function

The DVARP function calculates variance based on the entire population of selected database entries.

Now, enter the following formula in cell G17 to get the variance of yields from all pear trees and the apple trees with heights between 10 and 16 based on the entire population of selected database entries.

Things to Remember

• You must avoid blank rows in the criteria range. Otherwise, the function may disregard all of the specified conditions.
• You can use wild cards as criteria. For example, A* indicates that the texts must be started with the letter A.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know how to use all of the database functions in Excel. Please let us know if this article has satisfied your needs. You can also use the comment section below for further queries or suggestions.

<< Go Back to Excel Database Functions | Excel Function Categories | Excel Functions | Learn Excel