When should I use DPRODUCT instead of other database functions like DSUM or DAVERAGE?

DPRODUCT multiplies values instead of summing or averaging them, so use it when you need a compound or cumulative multiplication of field values. Real-world examples are rare—DSUM and DAVERAGE are far more common. DPRODUCT is useful in scenarios where field values represent growth factors or discount multipliers that compound together.

Why did my DPRODUCT formula return 1?

The formula returned 1 because no rows matched your criteria. Unlike DSUM (which returns 0) or DCOUNT (which returns 0), DPRODUCT uses 1 as the identity element for multiplication. This is correct behavior, but it can be confusing—double-check that your criteria are written correctly and actually match rows in your data.

Can DPRODUCT handle text criteria like warehouse names and SKU codes?

Yes, you can use text criteria to filter matching rows, but the field column being multiplied must always be numeric. You can filter by Warehouse='Boston' or SKU='SKU001', but the values in the Cost or On Hand columns must be numbers to multiply successfully.

How do I write criteria for multiple conditions, like "Boston warehouse AND on-hand greater than 100"?

Add additional columns to your criteria range, one for each condition. Create a criteria range with columns 'Warehouse' and 'On Hand', with values 'Boston' and '>100' below them. DPRODUCT will match only rows satisfying all conditions simultaneously using AND logic.

DPRODUCT function

DPRODUCT returns the product of all numeric values in a database field that meet your specified selection criteria.

=DPRODUCT(database, field, criteria)

Generate a DPRODUCT formula

Describe what you need. The generator will reach for DPRODUCT where DPRODUCT is the right tool, and tell you when it is not.

Arguments

How DPRODUCT reads its arguments

Argument	Required	Description
database	Required	The entire data range including column headers; must be a rectangular range with consistent structure across all rows.
field	Required	The column name (as quoted text) or column number (1-indexed) indicating which column to multiply; must contain numeric values.
criteria	Required	A range with column headers and matching values, defining which rows to include in the product calculation; use comparison operators like '<40' for conditional matching.

Returns

Returns a number representing the product of all matching field values.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate the product of costs for items in a specific warehouse

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU001	Boston	150	50	2.5
SKU001	Chicago	200	50	2.5
SKU002	Boston	30	40	1.5
SKU002	Chicago	75	40	1.5
SKU003	Boston	200	100	3
SKU003	Chicago	120	100	3
SKU004	Boston	25	50	0.99
SKU004	Chicago	15	50	0.99

=DPRODUCT(A1:E9, "Cost", A11:B12)

Result: 11.1375

DPRODUCT filters the inventory to rows where Warehouse='Boston' and multiplies their costs: 2.50 × 1.50 × 3.00 × 0.99 = 11.1375. This pattern is useful when costs represent factors that compound together, such as successive discount multipliers.

2. Find the product of quantities for a specific product across warehouses

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU001	Boston	150	50	2.5
SKU001	Chicago	200	50	2.5
SKU002	Boston	30	40	1.5
SKU002	Chicago	75	40	1.5
SKU003	Boston	200	100	3
SKU003	Chicago	120	100	3
SKU004	Boston	25	50	0.99
SKU004	Chicago	15	50	0.99

=DPRODUCT(A1:E9, "On Hand", A13:B14)

Result: 30000

The formula matches all rows where SKU='SKU001' (Boston and Chicago locations) and multiplies their on-hand quantities: 150 × 200 = 30,000. This represents scenarios where inventory levels across multiple locations compound in total value or impact.

3. Calculate the product of quantities for items below their reorder threshold

SKU	Warehouse	On Hand	Reorder Point	Cost
SKU001	Boston	150	50	2.5
SKU001	Chicago	200	50	2.5
SKU002	Boston	30	40	1.5
SKU002	Chicago	75	40	1.5
SKU003	Boston	200	100	3
SKU003	Chicago	120	100	3
SKU004	Boston	25	50	0.99
SKU004	Chicago	15	50	0.99

=DPRODUCT(A1:E9, "On Hand", A15:B16)

Result: 11250

DPRODUCT applies the criteria 'On Hand < 40' to find understocked items (SKU002-Boston with 30, SKU004-Boston with 25, SKU004-Chicago with 15) and multiplies them: 30 × 25 × 15 = 11,250. Comparison operators in criteria enable conditional matching beyond exact values.

Common errors

Which DPRODUCT error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The field column contains text values or mixed text/number entries that cannot be multiplied together.	Ensure the specified field contains only numeric data. If it has text entries, either select a different field or filter your database to exclude rows with non-numeric values before applying DPRODUCT.
#NAME?	The field parameter references a column header name that doesn't exist in the database range, or the name is misspelled.	Verify the field name exactly matches a column header in your database, including capitalization and spacing. Alternatively, use the column number (e.g., 5 for the fifth column) instead of the column name.
#REF!	The database range reference points to cells that have been deleted or moved, breaking the formula's link to the data.	Update the database range to reference the current location of your data. Use absolute references ($A$1:$E$9) to prevent automatic adjustments if you move the formula to other cells.

Tips and when to use something else

DPRODUCT returns 1 when no rows match your criteria, not 0—this is the multiplicative identity. If your result seems unexpectedly high, verify your criteria are filtering rows correctly.
For complex conditional multiplication with multiple criteria using OR logic, consider PRODUCT combined with IF statements or use a more flexible approach with SUMPRODUCT.
Ensure all values in your field column are numeric; even a single text entry will cause a #VALUE! error throughout the calculation.
Use comparison operators in criteria (<, >, <=, >=, <>) to match ranges of values: a criteria range with 'On Hand' and '<40' filters all stock below 40 units.

Related functions

DSUMDSUM sums values in a database column that meet specified criteria—use it to total filtered column values from a data table.DAVERAGECalculates the average of values in a database field that meet specified criteria, useful for finding filtered averages without creating separate tables.DCOUNTDCOUNT counts cells in a database field that meet specified criteria, returning the total count of matching non-blank cells.DMAXReturns the maximum numeric value from a database column where all rows match the specified filter criteria conditions.PRODUCTReturns the product of all numeric values supplied as arguments, multiplying them together into a single result.

Frequently asked questions

When should I use DPRODUCT instead of other database functions like DSUM or DAVERAGE?: DPRODUCT multiplies values instead of summing or averaging them, so use it when you need a compound or cumulative multiplication of field values. Real-world examples are rare—DSUM and DAVERAGE are far more common. DPRODUCT is useful in scenarios where field values represent growth factors or discount multipliers that compound together.
Why did my DPRODUCT formula return 1?: The formula returned 1 because no rows matched your criteria. Unlike DSUM (which returns 0) or DCOUNT (which returns 0), DPRODUCT uses 1 as the identity element for multiplication. This is correct behavior, but it can be confusing—double-check that your criteria are written correctly and actually match rows in your data.
Can DPRODUCT handle text criteria like warehouse names and SKU codes?: Yes, you can use text criteria to filter matching rows, but the field column being multiplied must always be numeric. You can filter by Warehouse='Boston' or SKU='SKU001', but the values in the Cost or On Hand columns must be numbers to multiply successfully.
How do I write criteria for multiple conditions, like "Boston warehouse AND on-hand greater than 100"?: Add additional columns to your criteria range, one for each condition. Create a criteria range with columns 'Warehouse' and 'On Hand', with values 'Boston' and '>100' below them. DPRODUCT will match only rows satisfying all conditions simultaneously using AND logic.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17