DPRODUCT Function (LibreOffice Calc)
The DPRODUCT function multiplies numeric values in a database column that match a set of criteria. It is part of the database function family and supports structured, criteria-based filtering.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
What the DPRODUCT Function Does
- Multiplies numeric values in a specified column
- Applies criteria-based filtering using a criteria range
- Supports multiple criteria columns
- Supports AND/OR logic via criteria layout
- Works with structured database ranges
It is designed to be precise, structured, and ideal for multiplicative aggregation.
Syntax
DPRODUCT(database; field; criteria)
Arguments
-
database:
A range where the first row contains column labels. -
field:
The column to multiply.- Use column label in quotes (recommended)
- Or use column index (1 = first column)
-
criteria:
A range containing column labels and one or more criteria rows.
Basic Examples
Assume a table in A1:C6:
| A (Item) | B (Qty) | C (Factor) |
|---|---|---|
| A | 2 | 1.5 |
| B | 3 | 2.0 |
| C | 4 | 1.2 |
| A | 5 | 1.1 |
| B | 2 | 1.3 |
Criteria in E1:E2:
| Item |
|---|
| A |
Multiply Factor values for Item A
=DPRODUCT(A1:C6; "Factor"; E1:E2)
Returns 1.5 * 1.1 = 1.65.
Multiply Qty values for Item B
=DPRODUCT(A1:C6; "Qty"; E1:E2)
Returns 3 * 2 = 6.
Using field index
=DPRODUCT(A1:C6; 3; E1:E2)
Multiplies values in column 3 (Factor).
Advanced Examples
Multiply values where Qty > 2
Criteria:
| Qty |
|---|
| >2 |
Formula:
=DPRODUCT(A1:C6; "Factor"; E1:E2)
Multiply values where Item = “B” AND Factor > 1.2
Criteria:
| Item | Factor |
|---|---|
| B | >1.2 |
Formula:
=DPRODUCT(A1:C6; "Factor"; E1:F2)
OR logic (multiple rows)
Criteria:
| Item |
|---|
| A |
| C |
Formula:
=DPRODUCT(A1:C6; "Qty"; E1:E3)
Multiply values where Item begins with “A”
Criteria:
| Item |
|---|
| A* |
Formula:
=DPRODUCT(A1:C6; "Factor"; E1:E2)
Multiply using dynamic criteria
=DPRODUCT(A1:C6; "Factor"; H1:I2)
Multiply only positive values
Criteria:
| Factor |
|---|
| >0 |
Formula:
=DPRODUCT(A1:C6; "Factor"; E1:E2)
Multiply non-blank values
Criteria:
| Factor |
|---|
| <>"" |
Formula:
=DPRODUCT(A1:C6; "Factor"; E1:E2)
Edge Cases and Behavior Details
DPRODUCT multiplies only numeric values
Text, blanks, and errors are ignored.
If no numeric values match → returns 1
Because multiplication identity = 1.
field can be:
- Column label
- Column index
- Cell containing label
criteria must include column labels
Exact match required.
criteria supports:
- Comparison operators
- Wildcards
- Multiple rows (OR)
- Multiple columns (AND)
DPRODUCT of an error in database → error ignored
DPRODUCT of an error in criteria → error returned
Criteria rows:
- Each row = OR
- Each column = AND
Common Errors and Fixes
DPRODUCT returns 1 unexpectedly
Cause:
- No numeric values match criteria
- Criteria labels don’t match database labels
- Criteria misaligned
Fix:
- Ensure labels match exactly
- Ensure criteria range includes labels
Err:502 — Invalid argument
Occurs when:
- field is invalid
- database range malformed
Criteria not applied
Cause:
- Criteria labels not identical to database labels
Best Practices
- Use DPRODUCT for compounding and multiplicative aggregation
- Use column labels instead of index numbers
- Keep criteria ranges small and clearly labeled
- Use wildcards for flexible text matching
- Use multiple criteria rows for OR logic
- Use multiple criteria columns for AND logic
- Use FILTER (modern Calc) for dynamic extraction
Related Patterns and Alternatives
- Use DSUM for summation
- Use DAVERAGE for conditional averages
- Use DMAX and DMIN for extrema
- Use DCOUNT or DCOUNTA for counting
- Use DGET for single-record retrieval
- Use PRODUCT for simple multiplication without criteria
- Use FILTER for dynamic row extraction
By mastering DPRODUCT and its companion database functions, you can build powerful, structured, and criteria-driven data workflows in LibreOffice Calc.