SUMPRODUCT
Multiply corresponding values in two or more ranges and sum the results — and, with a condition inside, count or total the rows that meet it.
Syntax
SUMPRODUCT(array1, [array2], ...)| Argument | Required | Description |
|---|---|---|
array1 | Required | The first range, or an expression that produces one, such as B2:B6*C2:C6 or --(A2:A6="East"). |
array2 | Optional | Further ranges or arrays of the same shape. Values are multiplied position by position. |
Examples
Every example below was executed against VisiGrid engine 0.55.1 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=SUMPRODUCT(B2:B6,C2:C6) | 67 | Quantity times price on each row, then added — revenue without a helper column. |
=SUMPRODUCT(--(A2:A6="East")) | 3 | A condition becomes TRUE/FALSE per row, and -- turns those into 1s and 0s to count. |
=SUMPRODUCT((A2:A6="East")*B2:B6*C2:C6) | 37 | Revenue for East only. Multiplying by the condition zeroes the other rows. |
=SUMPRODUCT(--(B2:B6>D2:D6)) | 2 | Rows where quantity beat target — a row-by-row comparison of two columns, which COUNTIFS cannot express. |
=SUMPRODUCT(B2:B3,B2:B4) | SUMPRODUCT ranges must have the same shape. Argument 1 is 2x1, argument 2 is 3x1. | Arrays must be the same shape. |
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. Blank cells count as 0.
Counting and summing with conditions
A comparison over a range, such as A2:A6="East", produces one TRUE or FALSE
per row. Inside SUMPRODUCT that becomes a counter or a filter:
=SUMPRODUCT(--(A2:A6="East")) → 3
=SUMPRODUCT((A2:A6="East")*B2:B6*C2:C6) → 37The double minus converts TRUE and FALSE into 1 and 0. Multiplying by the
condition does the same thing, which is why the second formula needs no --.
Combine conditions with * for AND and + for OR.
SUMIFS and COUNTIFS are
simpler when the condition is “this column equals that value”. SUMPRODUCT is
for the questions they cannot ask — comparing two columns row by row, or
weighting each row by a calculation:
=SUMPRODUCT(--(B2:B6>D2:D6)) → 2TRUE is not 1 until you say so
In a computed array, SUMPRODUCT counts only numbers, as Excel does:
SUMPRODUCT(A2:A6="East") is 0, because the TRUE values are skipped. Put --
in front of the condition, or multiply it by something, to make it count.
Every range must be the same shape; mismatched sizes give an error rather than a wrong answer.
Excel compatibility
Matches Excel, including conditions inside the arguments and treating TRUE/FALSE in a computed array as 0 unless converted with --.