SUMPRODUCT
Multiply corresponding values in two or more ranges and sum the results.
Syntax
SUMPRODUCT(array1, [array2], ...)| Argument | Required | Description |
|---|---|---|
array1 | Required | The first range. |
array2 | Optional | Further ranges of the same shape. Values are multiplied position by position. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=SUMPRODUCT(A1:A1,A4:A4) | 200 | 10 times 20. |
=SUMPRODUCT(A4:A5,A4:A5) | 1300 | 20 squared plus 30 squared. |
=SUMPRODUCT(--(A2:A3>1)) | #VALUE! Array arithmetic not supported | The conditional-count idiom every other spreadsheet uses. Documented as a gap, and checked as one. |
Quantity times price, in one cell
The classic use needs no helper column:
=SUMPRODUCT(quantities, prices)Each row multiplied by its own price, then totalled. Doing it with a helper
column and SUM gives the same answer and takes a column;
this takes a cell.
Counting with conditions does not work yet
In most spreadsheets SUMPRODUCT doubles as a conditional counter, because
TRUE behaves as 1 once coerced:
=SUMPRODUCT(--(A2:A100 > B2:B100)) ← #VALUE! here: array arithmetic is unsupportedThat is the classic way to ask questions COUNTIFS has
no grammar for, such as comparing two columns row by row. It does not work
here — array arithmetic is not supported, so the comparison inside the
parentheses fails before SUMPRODUCT sees it.
Until it lands, a helper column does the same job: put the comparison in one
column, then COUNTIF it. More cells, same answer, and it is legible to whoever
opens the file next.
Every range must be the same shape; mismatched sizes give an error rather than a wrong answer.
Excel compatibility
Matches Excel.