While working in Excel, we often need to use various Database Functions. The **DPRODUCT **function is one of the most widely used database functions in Excel. The **DPRODUCT **function provides the product of the values of a specific column from a database that meets certain conditions. The number of conditions can be single, or multiple. In this article, we will discuss six practical examples of how to use the **DPRODUCT **function in Excel. So, let’s start this article and explore these examples.

**Excel DPRODUCT Function (Quick View)**

The overview of the **DPRODUCT **function is demonstrated in the following image. We will discover more about using the **DPRODUCT **function in various scenarios in later portions of this article.

**Table of Contents**Expand

## Introduction to DPRODUCT Function in Excel

The **DPRODUCT** function is one of **the Database Functions**. Let’s familiarize ourselves with **Syntax**, **Argument**, and **Return Value **subsections of the **DPRODUCT **function to understand the basics of the function.

**Summary:**

- Returns the product of a defined field from a database that matches specified criteria.

**Syntax:**

Syntax of the **DPRODUCT **function is:

`=DPRODUCT(database,field,criteria)`

**Arguments:**

Argument | Required/Optional | Explanation |
---|---|---|

database |
Required | The range of cells that construct the database. It is to be noted that the first row of the database should be the column headers. |

field |
Required | It defines the column from which values are extracted. The field argument can be entered as Column Label, Column Index Number, or Cell Reference. |

criteria |
Required | It is the range of cells that has the criteria enlisted in them. The first row of the range should be the column headers of the database. |

**Return Value:**

The **DPRODUCT** function returns the product of the values from the specified field in the database which matches the defined criteria.

**Version:**

- Available from
*Excel 2010*.

**Remarks:**

- You can use any range size as a
**database**. But make sure that you have at least**one row**of data below the**column headers**. - You can place the
**criteria table**anywhere on the worksheet. But it’s good practice not to place the criteria table below the database. Because you might face difficulties in case of future addition of data in the database. - One thing to remember is that you can’t
**overlap**the criteria table with the database in any circumstances. - If you want to do an operation on an entire column in a database, insert a
**blank row**below the column headers in the criteria table.

## DPRODUCT Function in Excel: 6 Suitable Examples

In this section of the article, we will discuss 6** **practical examples of using the **DPRODUCT **function in Excel. Let’s say, we have the **Sales Data **of XYZ Fruit Store as our dataset. Our goal is to find the product of** Quantities **that match specific criteria by using the **DPRODUCT **function.

Not to mention, we used the *Microsoft Excel 365* version for this article; however, you can use any version according to your preference.

### 1. Using DPRODUCT Function for Single Criterion

In the first example, we will use the **DPRODUCT **function for a **single **criterion. Let’s follow the steps mentioned below to do this.

__Steps:__

- Firstly, create the following
**Criteria Table**and the output table.

Here, in the **Criteria Table**, we have only **one criterion**. That is the name of the **Fruit **should be **Apple**.

- After that, use the following formula in cell
**B24**.

`=DPRODUCT(B4:E15,C4,B19:B20)`

Here, the range of cells **B4:E15** indicates the specified **database**, cell **C4 **refers to the heading of the **Quantity** column, and the range **B19:B29** represents the **range of criteria**.

- Afterward, press
**ENTER**.

As a result, you will have the **Product of Quantities **that have “**Apple**” in the **Fruit **column as shown in the following image.

### 2. Utilizing DPRODUCT Function for Multiple Criteria

In the second example, we will utilize the **DPRODUCT **function for **multiple **criteria. For instance, we will use 3** **criteria to demonstrate this example. Now, let’s follow the instructions outlined below.

__Steps:__

- Firstly, create the following
**Criteria Table**and the output table, as shown in the image below.

Here, we used **three **criteria. They are:

*Name of the***Fruit**should be**Apple**.*Quantity**should be***greater than 30**.*Unit price**should be more than $***3**.

- Following that, apply the formula given below in cell
**B23**.

`=DPRODUCT(B4:E15,C4,B19:D20)`

Here, the range of cells **B19:D20** indicates the **range **of the **Criteria Table**.

- Subsequently, hit
**ENTER**.

Consequently, you will have the **Product of Quantities **that match the **Criteria Table**, in cell **B23**.

### 3. Applying DPRODUCT Function with Column Label in Field Argument

In the **field **argument, we can use **three **different types of inputs. They are given below.

*Column Label**Column Index Number**Cell Reference*

In this section of the article, we will use the **Column Label **option to specify the **field **argument in the **DPRODUCT **function. **Column Label **is nothing but the **heading of the column **which has the values that we will multiply. Now, let’s follow the steps mentioned below.

__Steps:__

- Firstly, use the following formula in cell
**B23**.

`=DPRODUCT(B4:E15,"Quantity",B19:D20)`

Here, “**Quantity**” represents the **field **argument.

- After that, press
**ENTER**.

Subsequently, you will have the **Product of Quantities **in cell **B23**, as demonstrated in the following image.

### 4. Employing DPRODUCT Function with Column Index Number in Field Argument

Now, we will use the Column Index Number to define the **field **argument in the **DPRODUCT **function. The column index number is simply the serial number of the column which contains the values that we will multiply. It is to be noted that the first column of the selected database is indexed as **1**. For example, if we use the values of the **Quantity **column, the column index number will be 2. On the other hand, if we use the values of the **Unit Price **column, the column index number will be **3**.

Now, let’s use the instructions outlined in the following section to do this.

__Steps:__

- Firstly, apply the following formula in cell
**B23**.

`=DPRODUCT(B4:E15,2,B19:D20)`

Here, **2 **is the **column index number**, which indicates the **field **argument.

- After that, hit
**ENTER**.

As a result, you will have the following output in cell **B23**, as shown in the following picture.

### 5. Using DPRODUCT Function with Cell Reference in Field Argument

At this point, we will use **Cell Reference** to specify the **field **argument in the **DPRODUCT **function. A **Cell Reference **locates a cell in the worksheet by using the combination of the column letter and the row number. It is also known as the **Cell Address**. Now, let’s follow the steps mentioned below.

__Steps:__

- Firstly, apply the following formula in cell
**B23**.

`=DPRODUCT(B4:E15,C4,B19:D20)`

Here, the cell reference **C4 **indicates the column header of the **2nd column**, which is the **field **argument.

- Following that, hit
**ENTER**.

Consequently, you will have the **Product of Quantities **in cell **B23 **as shown in the following image.

### 6. Implementing DPRODUCT Function with Wildcards

In this section of the article, we will implement the **DPRODUCT **function with **wildcards**. Generally, a **wildcard **is a special symbol that allows us to do partial matching inside an Excel formula. In the **DPRODUCT **function, we will use the **wildcards **in the **criteria **argument. Here are some examples of the wildcard criteria along with their meaning.

Criteria with Wildcard |
Meaning |
---|---|

Pen |
Exactly match “Pen”. |

*en |
Ends with “en”. |

Pe* |
Starts with “Pe”. |

So, let’s use the instructions discussed in the following section to do this.

__Steps:__

- Firstly, create the following
**Criteria Table**as shown in the image below.

- After that, use the following formula in cell
**B23**.

`=DPRODUCT(B4:E15,C4,B19:D20)`

Here, in the **Criteria Table**, “***go**” means a word that ends with “**go**“, and **>=30** means the **Quantity **should be greater than or equal to **30**.

- Following that, hit
**ENTER**.

Subsequently, you will have the **Product of Quantities **that meets the specified criteria in the **Criteria Table **in cell **B23 **as demonstrated in the following picture.

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it yourself.

**Download Practice Workbook**

## Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to use the** DPRODUCT **function in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. Have a nice day!

**<< Go Back to Excel Functions ****|**** Learn Excel**