DPRODUCT Function in Microsoft Excel
Part 1: Introduction
Definition
The DPRODUCT function in Microsoft Excel is a database function that multiplies the values in a specific field or column of records in a list or database that match the conditions you specify.
Purpose
The DPRODUCT function performs a multiplication operation on selected records in a database or list based on specified criteria.
Syntax & Arguments
The syntax for the DPRODUCT function is as follows:
DPRODUCT(database, field, criteria) Arguments
Database: The range of cells that makes up the list or database. A database is 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.
Field: Indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as “Age” or “Yield,” or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria: The range of cells that contains the conditions that you specify. You can use any content for the criteria argument as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.
Return Value
The DPRODUCT function returns the product of selected values in a database or list based on the specified criteria.
Remarks
- You can use any range for the criteria argument if it includes at least one column label and at least one cell below the column label for specifying the condition.
- Do not place the criteria range below the list. If you add more information to the list, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot count the further information.
- Make sure that the criteria range does not overlap the list.
- To operate on an entire column in a database, enter a blank line below the column labels in the criteria range.
Part 2: Examples
Example 1
Purpose of Example
This example demonstrates how to use the DPRODUCT function to calculate the product of the yields from apple trees with a height between 10 and 16 feet and any pear trees.
Data Table and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit | Criteria |
| 2 | Apple | >10 | <16 | |||
| 3 | Pear | |||||
| 4 | ||||||
| 5 | Tree | Height | Age | Yield | Profit | Formula |
| 6 | Apple | 18 | 20 | 14 | $105 | |
| 7 | Pear | 12 | 12 | 10 | $96 | |
| 8 | Cherry | 13 | 14 | 9 | $105 | |
| 9 | Apple | 14 | 15 | 10 | $75 | |
| 10 | Pear | 9 | 8 | 8 | $77 | |
| 11 | Apple | 8 | 9 | 6 | $45 | |
| 12 | ||||||
| 13 | Formula | Description | Result | |||
| 14 | =DPRODUCT(A5:E11, “Yield”, A1:F3) | The product of the yields from apple trees with a height between 10 and 16 feet and any pear trees. | $800 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =DPRODUCT(A5:E11, “Yield”, A1:F3) | The product of the yields from apple trees with a height between 10 and 16 feet and any pear trees. | $800 |
Explanation
The DPRODUCT function in this example multiplies the “Yield” values of the apple trees with a height greater than 10 and less than 16 and all the pear trees. The result is $800.
Example 2
Purpose of Example
This example demonstrates how to use the DPRODUCT function to calculate the product of profits from cherry trees that are older than 12 years.
Data Table and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit | Criteria |
| 2 | Cherry | >12 | ||||
| 3 | ||||||
| 4 | ||||||
| 5 | Tree | Height | Age | Yield | Profit | Formula |
| 6 | Apple | 18 | 20 | 14 | $105 | |
| 7 | Pear | 12 | 12 | 10 | $96 | |
| 8 | Cherry | 13 | 14 | 9 | $105 | |
| 9 | Apple | 14 | 15 | 10 | $75 | |
| 10 | Pear | 9 | 8 | 8 | $77 | |
| 11 | Apple | 8 | 9 | 6 | $45 | |
| 12 | ||||||
| 13 | Formula | Description | Result | |||
| 14 | =DPRODUCT(A5:E11, “Profit”, A1:F3) | The product of the profits from cherry trees older than 12 years. | $105 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =DPRODUCT(A5:E11, “Profit”, A1:F3) | The development of the earnings from cherry trees older than 12 years. | $105 |
Explanation
The DPRODUCT function in this example multiplies the “Profit” values of the cherry trees older than 12 years. The result is $105.
Example 3
Purpose of Example
This example demonstrates how to use the DPRODUCT function to calculate the product of the heights of pear trees with a yield of less than 10.
Data Table and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit | Criteria |
| 2 | Pear | <10 | ||||
| 3 | ||||||
| 4 | ||||||
| 5 | Tree | Height | Age | Yield | Profit | Formula |
| 6 | Apple | 18 | 20 | 14 | $105 | |
| 7 | Pear | 12 | 12 | 10 | $96 | |
| 8 | Cherry | 13 | 14 | 9 | $105 | |
| 9 | Apple | 14 | 15 | 10 | $75 | |
| 10 | Pear | 9 | 8 | 8 | $77 | |
| 11 | Apple | 8 | 9 | 6 | $45 | |
| 12 | ||||||
| 13 | Formula | Description | Result | |||
| 14 | =DPRODUCT(A5:E11, “Height”, A1:F3) | The product of the heights from pear trees that have a yield of less than 10. | 108 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =DPRODUCT(A5:E11, “Height”, A1:F3) | The product of the heights from pear trees that have a yield of less than 10. | 108 |
Explanation
The DPRODUCT function in this example multiplies the “Height” values of the pear trees with a yield of less than 10. The result is 108.
Example 4
Purpose of Example
This example demonstrates how to use the DPRODUCT function nested with the IF function to calculate the product of the yields from apple trees with a height between 10 and 16 feet and any pear trees, but only if the total profit exceeds $200.
Data Table and Formulas
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit | Criteria |
| 2 | Apple | >10 | <16 | |||
| 3 | Pear | |||||
| 4 | ||||||
| 5 | Tree | Height | Age | Yield | Profit | Formula |
| 6 | Apple | 18 | 20 | 14 | $105 | |
| 7 | Pear | 12 | 12 | 10 | $96 | |
| 8 | Cherry | 13 | 14 | 9 | $105 | |
| 9 | Apple | 14 | 15 | 10 | $75 | |
| 10 | Pear | 9 | 8 | 8 | $77 | |
| 11 | Apple | 8 | 9 | 6 | $45 | |
| 12 | ||||||
| 13 | Formula | Description | Result | |||
| 14 | =IF(SUM(E6:E11)>200, DPRODUCT(A5:E11, “Yield”, A1:F3), “Profit too low”) | The product of yields from apple trees with a height between 10 and 16 feet and any pear trees, but only if the total profit exceeds $200. | $800 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =IF(SUM(E6:E11)>200, DPRODUCT(A5:E11, “Yield”, A1:F3), “Profit too low”) | The product of yields from apple trees with a height between 10 and 16 feet and any pear trees, but only if the total profit exceeds $200. | $800 |
Explanation
The DPRODUCT function in this example is nested within an IF function. The IF function checks if the total profit (calculated using the SUM function) exceeds $200. If it does, the DPRODUCT function calculates the product of the yields from apple trees with a height between 10 and 16 feet and any pear trees. If the total profit does not exceed $200, the formula returns the text “Profit too low”. In this case, the real payoff is $528, so the DPRODUCT function is executed, and the result is $800.
Example 5
Purpose of Example
This example demonstrates how to use the DPRODUCT function nested with the SUM function to calculate the total yield of all trees, but only if the total profit exceeds $200.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit |
| 2 | >10 | >200 | |||
| 3 | <16 | ||||
| 4 | |||||
| 5 | Tree | Height | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
| 11 | Apple | 8 | 9 | 6 | $45 |
| 12 | |||||
| 13 | Formula | Description | Result | ||
| 14 | =IF(SUM(E6:E11)>200, DPRODUCT(A5:E11, “Yield”, A1:B3), “Profit too low”) | The total yield of all trees, but only if the total profit exceeds $200. | 57 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =IF(SUM(E6:E11)>200, DPRODUCT(A5:E11, “Yield”, A1:B3), “Profit too low”) | The total yield of all trees, but only if the total profit exceeds $200. | 57 |
Explanation
The DPRODUCT function in this example is nested within an IF function. The IF function checks if the total profit (calculated using the SUM function) exceeds $200. If it does, the DPRODUCT function calculates the total yield of all trees. If the total profit does not exceed $200, the formula returns the text “Profit too low”. In this case, the real payoff is $528, so the DPRODUCT function is executed, and the result is 57.
Example 6
Purpose of Example
This example demonstrates how to use the DPRODUCT function nested with the VLOOKUP function to calculate the yield of a specific type of tree, given its name.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit |
| 2 | Apple | >10 | >200 | ||
| 3 | <16 | ||||
| 4 | |||||
| 5 | Tree | Height | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
| 11 | Apple | 8 | 9 | 6 | $45 |
| 12 | |||||
| 13 | Formula | Description | Result | ||
| 14 | =DPRODUCT(A5:E11, “Yield”, A1:E3) | The yield of apple trees. | 840 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =DPRODUCT(A5:E11, “Yield”, A1:E3) | The yield of apple trees. | 840 |
Explanation
The DPRODUCT function in this example is used to calculate the yield of apple trees. The criteria for the DPRODUCT function is set by the VLOOKUP function, which looks for the tree name “Apple” in the first column and returns the corresponding yield. The result is 840.
Example 7
Purpose of Example
This example demonstrates how to use the DPRODUCT function nested with the COUNTIF function to calculate the total yield of trees, but only if the number of apple trees exceeds 2.
Data Table and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tree | Height | Age | Yield | Profit |
| 2 | Apple | ||||
| 3 | |||||
| 4 | |||||
| 5 | Tree | Height | Age | Yield | Profit |
| 6 | Apple | 18 | 20 | 14 | $105 |
| 7 | Pear | 12 | 12 | 10 | $96 |
| 8 | Cherry | 13 | 14 | 9 | $105 |
| 9 | Apple | 14 | 15 | 10 | $75 |
| 10 | Pear | 9 | 8 | 8 | $77 |
| 11 | Apple | 8 | 9 | 6 | $45 |
| 12 | |||||
| 13 | Formula | Description | Result | ||
| 14 | =IF(COUNTIF(A6:A11, “Apple”)>2, DPRODUCT(A5:E11, “Yield”, A1:E3), “Not enough apple trees”) | The total yield of trees, but only if the number of apple trees exceeds 2. | 840 |
Result Table
| Formula | Description | Result |
|---|---|---|
| =IF(COUNTIF(A6:A11, “Apple”)>2, DPRODUCT(A5:E11, “Yield”, A1:E3), “Not enough apple trees”) | The total yield of trees, but only if the number of apple trees exceeds 2. | 840 |
Explanation
The DPRODUCT function in this example is nested within an IF function. The IF function checks if the number of apple trees (calculated using the COUNTIF function) exceeds 2. If it does, the DPRODUCT function calculates the total yield of all trees. If the number of apple trees does not exceed 2, the formula returns the text “Not enough apple trees”. In this case, the number of apple trees is 3, so the DPRODUCT function is executed, resulting in 840.
Part 3: Tips and Tricks
Criteria Range: Make sure the criteria range does not overlap with the list or database range. Also, do not place the bars range below the list.
Field Argument: You can specify the field argument by the column label (enclosed in double quotes) or by the position of the column in the list (a number without quotes).
Database Structure: The first row of your database should contain the column labels. The rows below have the data.
Blank Line in Criteria: If you want to operate on an entire column in a database, enter a blank line below the column labels in the criteria range.
Error Handling: If the DPRODUCT function finds no rows that meet the criteria, it returns an error. You can use the IFERROR function to handle this error.
Remember, the DPRODUCT function is a powerful tool in Excel for performing operations on specific records in a database or list based on certain criteria. Practice with different datasets and standards to get the hang of it!