SUMPRODUCT Function (LibreOffice Calc)
The SUMPRODUCT function in LibreOffice Calc multiplies corresponding elements in arrays and returns the sum of those products. It is one of the most powerful functions for weighted calculations, conditional logic, and array-based formulas.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the SUMPRODUCT Function Does ▾
- Multiplies corresponding elements in arrays
- Sums the resulting products
- Supports multiple arrays
- Allows conditional logic without SUMIFS
- Works with numeric arrays, expressions, and Boolean logic
- Does not require Ctrl+Shift+Enter
It is designed to be flexible, powerful, and universally compatible.
Syntax ▾
SUMPRODUCT(array1; [array2]; ...)
Arguments
- array1, array2, …
Ranges or arrays of equal dimensions.
Boolean expressions are allowed and automatically coerced to 1 (TRUE) or 0 (FALSE).
Basic Examples ▾
Weighted sum
=SUMPRODUCT(A1:A5; B1:B5)
Multiplies each A value by its corresponding B value and sums the results.
Dot product
=SUMPRODUCT(A1:A3; B1:B3)
Computes the vector dot product.
Sum of products across multiple arrays
=SUMPRODUCT(A1:A5; B1:B5; C1:C5)
Multiplies A×B×C element-wise, then sums.
Advanced Examples ▾
Conditional SUM (single condition)
=SUMPRODUCT(A1:A10; A1:A10 > 5)
Equivalent to summing only values greater than 5.
Conditional SUM (multiple conditions)
=SUMPRODUCT(A1:A10; (B1:B10="X") * (C1:C10>0))
Equivalent to SUMIFS but more flexible.
Conditional COUNT
=SUMPRODUCT(B1:B10="Yes")
Counts how many cells equal “Yes”.
Conditional AVERAGE
=SUMPRODUCT(A1:A10; B1:B10="X") / SUMPRODUCT(B1:B10="X")
Average of A where B = “X”.
SUMPRODUCT with MOD (periodic grouping)
=SUMPRODUCT(A1:A100; MOD(ROW(A1:A100); 2)=0)
Sums every second row.
SUMPRODUCT for matrix multiplication (single cell)
=SUMPRODUCT(A1:C1; A1:A3)
Equivalent to a 1×3 × 3×1 multiplication.
SUMPRODUCT for correlation components
=SUMPRODUCT((A1:A10 - AVERAGE(A1:A10)); (B1:B10 - AVERAGE(B1:B10)))
Forms the numerator of the correlation coefficient.
SUMPRODUCT for logical OR
=SUMPRODUCT((A1:A10="X") + (A1:A10="Y") > 0)
Counts rows where A is X or Y.
Common Errors and Fixes ▾
SUMPRODUCT returns Err:502
Occurs when:
- Arrays have mismatched dimensions
- A range reference is malformed
- A semicolon or colon is misplaced
SUMPRODUCT returns 0 unexpectedly
Possible causes:
- Boolean logic not coerced properly
- Text values in numeric arrays
- Empty cells treated as 0
Fix:
Use VALUE() or double‑unary (–) to coerce text to numbers.
SUMPRODUCT ignores text values
Cause:
- SUMPRODUCT treats text as 0
- Boolean expressions must be numeric
Fix:
Wrap text with VALUE() if needed.
Best Practices ▾
- Use SUMPRODUCT for multi-condition logic without SUMIFS
- Use Boolean expressions for filtering
- Ensure arrays have matching dimensions
- Use SUMPRODUCT for weighted averages and dot products
- Avoid mixing text and numbers in arrays
Related Patterns and Alternatives ▾
- Use PRODUCT for simple multiplication
- Use SUM for additive aggregation
- Use SUMIFS for structured conditional sums
- Use COUNTIFS for conditional counting
- Use MMULT for full matrix multiplication (Calc supports it)
By mastering SUMPRODUCT and its combinations with other math functions, you can build advanced, flexible models in LibreOffice Calc that handle weighted, conditional, and array-based logic with exceptional power.