How to Use the DPRODUCT Function in Google Sheets

Published on

The DPRODUCT function in Google Sheets multiplies the values in a specified numeric field in a table matching conditions.

While less commonly used in real-life scenarios, real-life examples of its application are rare to find even with extensive searching. Nonetheless, DPRODUCT remains invaluable for conducting conditional product calculations within structured datasets.

This tutorial explains how to use the DPRODUCT function with a real-life example.

As a side note, database functions work only with structured data, a data set with field labels (header row with labels), and no merged cells.

If you have such data and are not well-versed in functions to conditionally return a product, then DPRODUCT is your best bet.

DPRODUCT Function: Syntax and Arguments

Syntax:

DPRODUCT(database, field, criteria)

The DPRODUCT function has three arguments as per the syntax, and here is what they represent.

Arguments:

  • database: The structured table to consider. Each column in the table must contain a label in the top row, usually representing the data in the column.
  • field: Indicates the column in which to apply the product calculation. You can specify the column number (the first column will be numbered one) or the field label.
  • criteria: An array or range containing the conditions to apply filtering before product calculation.

Example of Using the DPRODUCT Function in Google Sheets

Assume you are awarded a job to install HT Panels and Lighting Poles amounting to USD 80,000. You will receive the payment as per the billing break-up approved.

This means each payment will be released upon the completion stage at the site, such as mobilization, supply of material, installation, testing, and commissioning.

Sample Data:

ItemQty (Nos)Payment StagePayment Percentage/Amount
HT Panel4Total$20,000.00
HT Panel4Mobilization5%
HT Panel4Supply of Material40%
HT Panel4Installation40%
HT Panel4Testing10%
HT Panel4Commissioning5%
Lighting Poles100Total$60,000.00
Lighting Poles100Mobilisation5%
Lighting Poles100Supply of Material60%
Lighting Poles100Installation20%
Lighting Poles100Testing10%
Lighting Poles100Commissioning5%

Assume you want to calculate the amount you get after supplying the Lighting Poles. Here we can use the DPRODUCT function in Google Sheets.

Let’s arrange the sample data and criteria range for the explanation.

Sample Data and Criteria Arrangement for the DPRODUCT Function

Sample Data Preparation:

Copy and paste the table above into an empty sheet of a Google Sheets file. Leave 4 blank rows above for spacing. So, the range here will be A5:D17.

Example of using the DPRODUCT function in Google Sheets

Criteria Preparation:

  1. In A1:D1, copy and paste the header of the table.
  2. In C2:C3, under the field ‘Payment Stage’, enter “Total” and “Supply of Material”.
  3. Under the field ‘Item’ in A2:A3, enter “Lighting Poles”.

This means we need to find the product of values in the filtered rows where A5:D17 matches “Lighting Poles” and C5:C7 matches either “Total” or “Supply of Material”.

We have properly arranged the database and criteria for the DPRODUCT function. Now coding the formula is just easy.

Formula:

=DPRODUCT(A5:D17, "Payment Percentage/Amount", A1:D3)

Where:

  • databaseA5:D17
  • field "Payment Percentage/Amount"
  • criteriaA1:D3

Replace “Supply of Material” in cell C3 with “Installation”. The DPRODUCT will return the installation amount of Lighting Poles.

Alternatives to the DPRODUCT Function in Google Sheets

If your data lacks structure, consider using a combination of FILTER + PRODUCT, SUMPRODUCT, or QUERY to calculate conditional products.

Unlike DPRODUCT, these functions may become complex depending on the number of criteria you want to apply.

The benefit is that they will likely work well with both structured and unstructured data, provided there are no merged cells in the range.

For example, we can replace our previous DPRODUCT formula with this FILTER and PRODUCT combo:

=PRODUCT(FILTER(D6:D17, (A6:A17="Lighting Poles")*((C6:C17="Total")+(C6:C17="Supply of Material"))))

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.