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.

**Watch Video – Use Database Functions in Excel **

**Table of Contents**Expand

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

**DSUM(database, field, criteria)**

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

`=DSUM(B4:F10, "Profit", 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.

`=DSUM(B4:F10, "Profit",B12:G14)`

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

`=DAVERAGE(B4:F10, "Yield", B12:C13)`

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.

`=DAVERAGE(B4:F10,3,B4:F10)`

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

`=DCOUNT(B4:F10, "Age", B12:G13)`

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

`=DCOUNTA(B4:F10, "Profit", B12:G13)`

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

`=DGET(B4:F10, "Yield", B12:B14)`

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

`=DGET(B4:F10,4,B12:G14)`

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

`=DMAX(B4:F10, "Profit", B12:G14)`

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

`=DMIN(B4:F10, "Profit", B12:C13)`

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

`=DPRODUCT(B4:F10, "Yield", B12:G14)`

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

`=DSTDEV(B4:F10, "Yield", B12:B14)`

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

`=DSTDEVP(B4:F10, "Yield", B12:B14)`

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

`=DVAR(B4:F10, "Yield", B12:B14)`

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

`=DVARP(B4:F10, "Yield", B12:B14)`

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

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