Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Quick View of DPRODUCT Function in Excel


Download Practice Workbook


Introduction to DPRODUCT Function in Excel

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:

Syntax of DPRODUCT Function in Excel

=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 to an entire column in a database, insert a blank row below the column headers in the criteria table.

6 Suitable Examples of Using DPRODUCT Function in Excel

In this section of the article, we will discuss six 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.

Sample Dataset

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.

Creating Criteria Table to use DRPODUCT Function for single criterion

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

Applying DPRODUCT formula for single criterion

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

Output obtained from DPRODUCT function for single criterion

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


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

Criteria table for demonstration of DRPODUCT function for multiple criteria

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

Using DPRODUCT function to find the Product of Quantities

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

Output obtained by using DPRODUCT function for multiple criteria

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


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.

Applying DPRODUCT function in cell B23.

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

Output got by using DPRODUCT function in Excel with column label in field criteria

Read More: Excel DSUM vs SUMIF Functions (2 Suitable Examples)


Similar Readings


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.

 Using DPRODUCT function to find the Product of Quantities in cell B23.

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

Output obtained by using PDODUCT function in Excel with column index number as field criteria

Read More: How to Use DMIN Function in Excel (4 Suitable Examples)


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.

Using DPRODUCT function in Excel with cell reference as field criteria 

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

Output obtained by using DPRODUCT function in cell B23.

Read More: How to Use Excel DSUM Function (4 Appropriate Examples)


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.

Criteria Table to use DPRODUCT function with wildcards in Excel

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

Using DPRODUCT function with wildcards in Excel

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.

Output got in cell B23 by using DPRODUCT Function with wildcards in Excel


Practice Section

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

Sample Practice Section provided in each worksheet of the 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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo