Syntax
SUMPRODUCT(array1, [array2, ...])
| Parameter | Description |
|---|---|
| array1 | Parameter of the SUMPRODUCT function. |
| [array2 | (Optional.) Parameter of the SUMPRODUCT function. |
| ...] | Parameter of the SUMPRODUCT function. |
Examples
| A | B | C | |
|---|---|---|---|
| 1 | Subject | Score | Credits |
| 2 | Math | 90 | 4 |
| 3 | English | 85 | 3 |
| 4 | Science | 78 | 4 |
| 5 | History | 92 | 3 |
Weighted average (e.g., grade calculation)
Formula
=SUMPRODUCT(B2:B5, C2:C5)/SUM(C2:C5)
Returns: 85.7 — weighted average where B has scores and C has credit hours/weights
Count with multiple conditions
Formula
=SUMPRODUCT((A2:A100="East")*(B2:B100="Shipped"))
Returns: 14 — counts rows where region is "East" AND status is "Shipped"
Conditional sum with multiple criteria
Formula
=SUMPRODUCT((A2:A100="East")*(B2:B100>1000)*C2:C100)
Returns: 42,500 — sums column C where region is East AND column B exceeds 1000
Common Errors
#VALUE!
The arrays have different dimensions. All arrays in SUMPRODUCT must have the same number of rows and columns.
#VALUE!
One of the arrays contains text in cells that need to be multiplied. Non-numeric values cause errors when multiplied.
Tips
Multi-criteria counting
SUMPRODUCT((cond1)*(cond2)) works like COUNTIFS but with more flexibility — you can use OR with +: (cond1)+(cond2)>0.
No Ctrl+Shift+Enter needed
Unlike array formulas, SUMPRODUCT handles arrays natively. You do not need to press Ctrl+Shift+Enter.
Boolean multiplication
TRUE*TRUE=1, TRUE*FALSE=0. This is why (A1="Yes")*(B1>10) works as AND logic in SUMPRODUCT.
Try SUMPRODUCT in Viztab
Import your data and use SUMPRODUCT with 370+ other formulas. No signup required.
Open Viztab