SUMPRODUCT Function (OpenOffice Calc)
The SUMPRODUCT function in OpenOffice Calc multiplies corresponding elements in arrays and returns the sum of those products. Learn syntax, examples, conditional logic, array behavior, and best practices.
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
- Enables conditional logic without array entry
- Works with ranges, expressions, and criteria
- Useful for weighted averages, filtering, and vector math
SUMPRODUCT is ideal when you need array‑level calculations without Ctrl+Shift+Enter.
Syntax ▾
SUMPRODUCT(array1; array2; ...)
Arguments:
- array1, array2, … — Ranges or expressions of equal dimensions
If only one array is provided, SUMPRODUCT simply sums its values.
Basic Examples ▾
Multiply and sum two ranges
=SUMPRODUCT(A1:A5; B1:B5)
Equivalent to:
A1*B1 + A2*B2 + ... + A5*B5
Weighted sum
=SUMPRODUCT(Weights; Values)
Weighted average
=SUMPRODUCT(A1:A10; B1:B10) / SUM(A1:A10)
Sum of a single range
=SUMPRODUCT(A1:A10)
Same as SUM(A1:A10).
Conditional Examples (No Array Formula Needed) ▾
Single condition
Sum values in B where A = “North”:
=SUMPRODUCT((A1:A100="North") * B1:B100)
Two conditions
=SUMPRODUCT((A1:A100="North") * (C1:C100="Q1") * B1:B100)
Numeric condition
=SUMPRODUCT((A1:A100>50) * B1:B100)
Excluding zeros
=SUMPRODUCT((A1:A100<>0) * A1:A100)
Between two dates
=SUMPRODUCT((A1:A100>=DATE(2025;1;1)) * (A1:A100<=DATE(2025;12;31)) * B1:B100)
Conditional count (COUNTIFS alternative)
=SUMPRODUCT(A1:A100="North")
Conditional boolean logic
OR condition:
=SUMPRODUCT(((A1:A100="North") + (A1:A100="South")) > 0)
Advanced Examples ▾
Dot product (vector math)
=SUMPRODUCT(A1:A3; B1:B3)
Matrix multiplication (single row × single column)
=SUMPRODUCT(A1:C1; E1:E3)
Conditional weighted average
=SUMPRODUCT((Region="North") * Sales * Weight) / SUMPRODUCT((Region="North") * Weight)
Frequency-like calculations
Count values between 10 and 20:
=SUMPRODUCT((A1:A100>=10) * (A1:A100<=20))
SUMPRODUCT across sheets
=SUMPRODUCT(Sheet1.A1:A10; Sheet1.B1:B10)
SUMPRODUCT with text-safe numeric conversion
=SUMPRODUCT(VALUE(A1:A10); B1:B10)
SUMPRODUCT for correlation components
=SUMPRODUCT((A1:A10 - AVERAGE(A1:A10)) * (B1:B10 - AVERAGE(B1:B10)))
Common Errors and Fixes ▾
SUMPRODUCT returns Err:502 (Invalid argument)
Occurs when:
- Arrays have mismatched dimensions
- A range contains text where numeric values are expected
- A malformed reference is used
SUMPRODUCT returns Err:508 (Missing parenthesis)
Usually caused by:
- Missing
) - Using commas instead of semicolons
SUMPRODUCT returns 0 unexpectedly
Possible causes:
- Criteria never match
- Text numbers not converted to numeric
- Boolean expressions not multiplied correctly
SUMPRODUCT includes values you expected it to ignore
SUMPRODUCT includes:
- Dates
- Times
- Numeric results of formulas
SUMPRODUCT excludes values you expected it to include
SUMPRODUCT ignores:
- Text numbers (
"123") unless wrapped in VALUE - Empty cells
- Logical values unless coerced
- Errors
Best Practices ▾
- Use SUMPRODUCT for multi‑criteria filtering
- Use multiplication (
*) for AND logic - Use addition (
+) for OR logic - Convert text numbers using VALUE when needed
- Keep arrays the same size to avoid errors
- Use named ranges for cleaner formulas
- Avoid extremely large arrays for performance reasons
SUMPRODUCT is the Swiss Army knife of spreadsheet analytics — it replaces dozens of specialized functions when used creatively.