Dynamic arrays
SUMPRODUCT function playground
Multiplies matching items in ranges and adds up the results.
The playground runs in your browser. Nothing you type is sent anywhere.
How SUMPRODUCT works
SUMPRODUCT multiplies the first items of each array together, then the second items, and so on, and adds up all the products.
Tests such as (B2:B11="East") produce TRUE/FALSE, which act as 1 and 0 when multiplied. That turns SUMPRODUCT into a flexible conditional sum or count, and it works in every version of Excel.
Syntax
=SUMPRODUCT(array1, [array2], ...)| Argument | What it means |
|---|---|
array1required | The first range or array. |
array2, ...optional | More ranges of the same size to multiply with it. |
Examples
| Formula | What it does | Result |
|---|---|---|
=SUMPRODUCT((B2:B11="East")*F2:F11) | Revenue for East, like SUMIF. | $8,700.00 |
=SUMPRODUCT((B2:B11="West")*(D2:D11="Laptop")*F2:F11) | West laptops, like SUMIFS. | $6,000.00 |
=SUMPRODUCT(--(E2:E11>=4)) | Count sales of 4 or more units. -- turns TRUE/FALSE into 1/0. | 4 |
=SUMPRODUCT(E2:E11,F2:F11)/SUM(E2:E11) | A weighted average: revenue weighted by units. | 1995.3125 |
Common mistakes
Ranges of different sizesEvery array must be the same size, or you get #VALUE!.
Text in a multiplied rangeText counts as 0 when you list arrays separately, but causes #VALUE! inside a multiplication.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.