➕ Math Function · advanced
SUMPRODUCT Function in Google Sheets
Multiplies corresponding elements in arrays and returns the sum. Used for weighted averages and complex conditional sums.
Syntax
SUMPRODUCT(array1, [array2, ...])Returns: Sum of element-wise products.
Excel equivalent: SUMPRODUCT (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| array1, array2, ... | Required | Arrays of same dimensions. |
Examples
Weighted average
=SUMPRODUCT(A2:A10, B2:B10) / SUM(B2:B10)Values × weights / sum of weights.
Multi-condition sum
=SUMPRODUCT((A2:A100="West") * (B2:B100>1000) * C2:C100)Sums C where A is West AND B > 1000.
Complex count
=SUMPRODUCT((A2:A100="West") * (MONTH(B2:B100)=6))Counts West from June — COUNTIFS can't take MONTH().
When to use an alternative
- SUMIFS — Simple conditions.
- ARRAYFORMULA + SUM — Cleaner syntax.
- QUERY — GROUP BY semantics.
Common errors and how to fix them
#VALUE!
Cause: Different dimensions.
Fix: Same shape required.
Returns 0
Cause: Text interferes.
Fix: Wrap with -- or *1 for numeric coercion.
Related functions
SUMIFS
Sums values that meet multiple conditions. The multi-criteria version of SUMIF, and the cleanest way...
ARRAYFORMULA
Applies a formula to an entire range at once, returning an array of results that 'spills' into the s...
QUERY
Runs a SQL-like query against a range. Google Sheets' most powerful function for filtering, sorting,...
SUM
Adds up numbers in a range or list of values. The first function nearly every spreadsheet user learn...
ABS
Returns the absolute value of a number....
Frequently Asked Questions
Still useful given SUMIFS?
When criterion uses MONTH/YEAR etc., for weighted means, for porting legacy Excel.
Boolean trick?
(A:A="X") returns TRUE/FALSE. SUMPRODUCT coerces to 1/0 when multiplied.