As far as I am concerned, the DPRODUCT function in Google Sheets is very rare in use. Maybe I am wrong. I don’t find much use of this function. Still, for those who want to use this database function, I am going to explain how to use it.
Unlike some other functions, this time I know I should address two things related to this formula.
- The first and foremost one is how to use the DPRODUCT function in Google Sheets.
- This second point is also important, i.e., the real-life use of the DPRODUCT function in Sheets.
Google Sheets DPRODUCT Function – Usage Instructions
DPRODUCT is a database function. Unlike it’s ‘siblings’ DCOUNT and DSUM, it’s not much popular among Google Sheets as well as Excel users.
As you may know, database functions are intended to use on structured data. Your data should have proper column labels and also no merged cells are allowed. That means you should follow these guidelines in the use of DPRODUCT function too.
The DPRODUCT function returns the product of values selected from an array or range similar to a database table.
In simple words, you can multiply numbers in any field in an array or range based on given criteria. That is the whole purpose of this function.
Syntax and Arguments of the DPRODUCT Function – Explained
Syntax:
DPRODUCT(database, field, criteria)
Understand the Function Arguments:
Database
It’s the range containing the data to consider. The first row of the range must contain column labels.
Field
It indicates which column in the database (range) to be extracted and operated on (multiplied). You can use column number or column label within double quotes to represent the Field.
Criteria
A range containing the conditions by which to filter the ‘database’ values before operating.
Understand How DPRODUCT Formula Operates in Sheets
This is not real-life use. I have included one real-life example to the use of DPRODUCT after this.
As I mentioned in the very beginning of this tutorial, I don’t find much use of this function in Sheets. Here I am just showing you how the formula operates.
=DPRODUCT(A5:E12,"Point",A1:E2)
In this DPRODUCT formula, A5:E12 is the Database, “Point” is the Filed and A1:E2 is the Criteria.
Actually, you just need to use A1:A2 as the criteria. That means the criteria is “Celia Saunders”.
But I normally copy paste all the labels in the Database, i.e. the row A5:E5, to use in Criteria. It has one advantage. What’s that?
If you want to use multiple criteria, you can simply put the criteria in the corresponding columns in B2:E2, or B2:E3 and so on.
See the above DPRODUCT formula example. Here the formula multiplies the values in Cell D6 and D10 which match the Criteria in the first column. These two cells are in the Field column which is “Point”.
You can also use the formula as below.
=DPRODUCT(A5:E12,4,A1:E2)
Here in this formula, I’ve used Field/column number instead of Field label.
How to use the criterion within the formula in DPRODUCT?
The answer is the below formula.
=DPRODUCT(A5:E12,"Point",{"Contestant";"Celia Saunders"})
If you want to know how to use comparison operators, date, etc. as criteria in the DPRODUCT formula, please follow this guide – How to Properly Use Criteria in DSUM in Google Sheets [Chart]. It’s related to DSUM, but the usage is the same in both the functions.
I know my above example doesn’t make much sense. Why should one want to multiply the points of a contestant, right? But I hope it served the purpose, i.e. to make you understand how the formula operates.
Now here is a real-life example to the use of the DPRODUCT function in Google Sheets.
Real-life Use of the DPRODUCT Function in Google Sheets
See the example below. You can use DPRODUCT formula in similar situations.
Database: A4:E14.
Field: 4 or “Values”.
Criteria: 1005.
I have arranged the data in a way suitable for the DPRODUCT to correctly operate. I mean you can sea each criterion repeats twice in A5:A14, one for Qty. and another for Rate (column 4).
The formula calculates the sales value of an item based on item code.
