How to Use DPRODUCT Function in Excel (6 Practical Examples)

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.

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:

Arguments:

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.

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.

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.

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.

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.

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.

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.

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