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.

**Table of Contents**hide

## Download Practice Workbook

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

## 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 the DVARP** function. 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)`

**Read More: ****How to Use DSUM Function with Multiple Criteria in Excel**

### 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)`

**Read More: How to Create a Database That Updates Automatically in Excel**

### 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 contains numbers and corresponds to the apple trees with heights between 10 and 16.

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

**Read More: ****How to Use DCOUNT Function in Excel (5 Suitable Examples)**

### 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)`

**Read More: How to create a database in Excel (make in 8 easy steps)**

### 5. The DGET Function

The **DGET function** extracts from a database a single record thatch 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)`

**Read More: ****Can Excel DGET Return Multiple Records [See 4 Solutions]**

**Similar Readings**

**How to Maintain Customer Database in Excel****Create Inventory Database in Excel (3 Easy Methods)****How to Create a Database with Form in Excel**

### 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)`

**Read More: How to Create a Simple Database in Excel VBA**

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

## 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. Do visit our **ExcelDemy** blog to explore more on excel. Stay with us and keep learning.