Contact
12 programs
Explore the site

SUMPRODUCT Function (OpenOffice Calc)

Math Intermediate OpenOffice Calc Introduction not established

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.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: Documented by Apache OpenOffice 4.1.16; original introduction not established.
  • Availability: Supported in Apache OpenOffice 4.1.16, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.

The SUMPRODUCT function in OpenOffice Calc multiplies corresponding elements in arrays and returns the sum of those products. It is one of the most powerful functions in Calc, enabling weighted calculations, conditional sums, multi‑criteria filtering, and advanced analytics — all without requiring explicit array formulas.

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.