How to use the DPRODUCT function
What is the DPRODUCT function?
The DPRODUCT function multiplies numbers that match a condition or criteria in a database.
Table of Contents
1. Introduction
What is DPRODUCT an abbreviation of?
DPRODUCT is an abbreviation of Database Product.
What is a database in this context?
Excel defines a database as a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Why use the DPRODUCT function?
The DPRODUCT function multiplies numbers that match a condition or criteria in a list/database whereas the PRODUCT function performs a calculation without a condition/criteria.
Where can you place the criteria range?
You can place your criteria range wherever you want on your worksheet, however, it is not recommended below the list/database. The function needs a blank row below the list to work properly.
What criteria characters are allowed?
Allowed criteria range characters are less than and greater than signs <>, use them to specify a criteria range. Also, asterisks * can be used to match partial strings.
What is a product?
A product is what you get when you multiply two numbers.
What is multiplication?
It is one of the four basic arithmetic operations. Multiplication means adding a number together multiple times. It is a faster way to add the same number over and over again.
For example:
5 x 3 means adding 5 together 3 times:
5 + 5 + 5 = 15
So multiplication is a shorthand for repeated addition.
What is repeated multiplication?
The PRODUCT faction returns the product of two or more numbers. If the numbers are repeated you can use exponentiation to calculate the result.
Repeated multiplication involves using multiplication to find the result of multiplying a number by itself multiple times.
For example:
- Repeatedly multiplying 5 by itself 3 times is written: 5 x 5 x 5
- The result is:
5 x 5 x 5 = 125
Repeated multiplication and exponentiation describe the same fundamental mathematical operation, 2 x 2 x 2 x 2 = 24
Exponentiation lets you shorten repeated multiplication considerably.
The POWER function lets you calculate a number multiplied x repeated times.
For example 5 * 5 * 5 = 53
=POWER(5,3) equals 125
you also have the alternative to use the ^character like this:
=5^3 equals 125
How to include the entire database in the calculation?
To include the entire list/database enter a blank line below the criteria range column labels.
2. Syntax
DPRODUCT (database, field, criteria)
| database | Required. The cell reference to a list or database. |
| field | Required. The field argument lets you choose which column to use. You can use the column name enclosed with double quotation marks or the corresponding column number. |
| criteria | Required. A cell reference to the criteria range. The criteria range needs to have column labels and at least one condition below the column label. |
3. Example 1
A company has six production lines, A102, A103, A099, A412, A341, A340, and A202. The probability of a defective product coming from line A102 is 0.1, A103 is 0.2, A099 is 0.05, A412 is 0.15, A341 is 0.3 , A340 is 0.07, and A202 is 0.13. The production lines are in three different categories named A, B, and C. What is the probability that all three products are defective in category A?
The data is in cell range B5:D12 in the image above, here is that data:
| Product | Category | Probability |
| A102 | A | 0.1 |
| A103 | B | 0.2 |
| A099 | C | 0.05 |
| A412 | C | 0.15 |
| A341 | B | 0.3 |
| A340 | A | 0.07 |
| A202 | C | 0.13 |
The conditions is in cell range B2:D3:
| Product | Category | Probability |
| A |
The arguments are:
database = B6:D12
field = 3
criteria = B2:D3
Formula in cell B15:
The formula multiplies numbers from column 3 (D) with the condition that the category is "A". The following probabilities are in category A: 0.1 and 0.07, the product is 0.1 * 0.7 equals 0.007 This value matches the output in cell B15.
4. Example 2
This example demonstrates AND logic between conditions.
A manufacturing plant has six production lines, A102, A103, A099, A412, A341, A340, and A202. The probability of a defective product coming from line A102 is 0.1, A103 is 0.2, A099 is 0.05, A412 is 0.15, A341 is 0.3 , A340 is 0.07, and A202 is 0.13. The production lines are in three different categories named A, B, and C. What is the probability that two products, one from each line, are defective in category C and have a probability value larger than 0.1?
The data is in cell range B5:D12 in the image above, here is that data:
| Product | Category | Probability |
| A102 | A | 0.1 |
| A103 | B | 0.2 |
| A099 | C | 0.05 |
| A412 | C | 0.15 |
| A341 | B | 0.3 |
| A340 | A | 0.07 |
| A202 | C | 0.13 |
The conditions is in cell range B2:D3:
| Product | Category | Probability |
| C | >0.1 |
The arguments are:
database = B6:D12
field = 3
criteria = B2:D3
Formula in cell B15:
The formula multiplies numbers from column 3 (D) with the condition that the category is "C" and the probability is larger than 0.1. Both conditions must match on the same row, in other words, AND logic is performed. The following probabilities are in category C: 0.05, 0.15 and 0.13, however, 0.05 is not larger than 0.1. The product is 0.15 * 0.13 equals 0.0195 This value matches the output in cell B15.
5. Example 3
This example demonstrates the asterisk which is a wildcard character. It matches 0 (zero) to any number of characters.
A factory has six production lines, A102, A103, A099, A412, A341, A340, and A202. The probability of a defective product coming from line A102 is 0.1, A103 is 0.2, A099 is 0.05, A412 is 0.15, A341 is 0.3 , A340 is 0.07, and A202 is 0.13. The production lines are in three different categories named A, B, and C. What is the probability that two products are defective, one from each line, that begins with B in column "Product"?
The data is in cell range B5:D12 in the image above, here is that data:
| Product | Category | Probability |
| A102 | A | 0.1 |
| B103 | B | 0.2 |
| A099 | C | 0.05 |
| A412 | C | 0.15 |
| B341 | B | 0.3 |
| A340 | A | 0.07 |
| A202 | C | 0.13 |
The conditions is in cell range B2:D3:
| Product | Category | Probability |
| B* |
The arguments are:
database = B6:D12
field = 3
criteria = B2:D3
Formula in cell B15:
The formula multiplies numbers from column 3 (D) with the condition that the product line begins with "B". The asterisk is a wildcard character that lets you match 0 (zero) to any number of character. The following probabilities begins with "B": 0.2 and 0.3. The product is 0.2 * 0.3 equals 0.06 This value matches the output in cell B15.
Functions in 'Database' category
The DPRODUCT function function is one of 11 functions in the 'Database' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form