SUMPRODUCT in Excel: how to use it
SUMPRODUCT multiplies columns row by row and adds up the results. Handy for totals without a helper column and for weighted averages.
Try the first exercises for freeIn the lesson The SUMPRODUCT function
What does SUMPRODUCT do?
SUMPRODUCT multiplies columns with each other row by row and adds up all the results. With a column of prices and a column of quantities, you get the total amount straight away, without a helper column.
Because a comparison gives TRUE or FALSE, and multiplying turns that into 1 or 0, you can also put conditions in SUMPRODUCT. That way it works like a SUMIFS that can do even more, in older versions of Excel too.
When do you use SUMPRODUCT?
- The total of an order with prices and quantities: =SUMPRODUCT(B2:B10,C2:C10).
- The revenue of one region: =SUMPRODUCT((A2:A100="Leeds")*C2:C100).
- A weighted average, such as marks with weights: =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6).
- Adding up the sales from March: =SUMPRODUCT((MONTH(A2:A100)=3)*B2:B100).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =SUMPRODUCT(A2:A3,B2:B3)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SUMPRODUCT | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SUMPRODUCT |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
array1 | The first column of numbers, such as the prices. | A2:A3 |
[array2] | Optional: the column you multiply by, such as the quantities. The same length as the first. | B2:B3 |
, | A comma separates the arguments from each other. | , |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
SUMPRODUCT step by step
D1 contains =SUMPRODUCT(A2:A3,B2:B3). 10×3 + 25×2. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click D1 and type SUMPRODUCT with a bracket.
=SUMPRODUCT( - Select the prices, A2:A3, and type a comma.
=SUMPRODUCT(A2:A3, - Select the quantities, B2:B3. Both ranges have to be the same size.
=SUMPRODUCT(A2:A3,B2:B3 - Close the bracket and press Enter: 10 × 3 + 25 × 2 = 80.
=SUMPRODUCT(A2:A3,B2:B3)
More examples
With a condition
E1 contains =SUMPRODUCT((A2:A6="Leeds")*C2:C6). The comparison gives 1 for Leeds and 0 for the rest. Multiplied by the revenue, only the revenue from Leeds is left.
A weighted average
E1 contains =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). Maths counts four times, music once: the weighted average is 14.75.
Two conditions
E1 contains =SUMPRODUCT((A2:A6="Leeds")*(B2:B6="Laptop")*C2:C6). Two comparisons multiplied: only the rows where both are true count.
Where it often goes wrong
- All ranges must be the same size.
Practise this in the lesson The SUMPRODUCT function. The first exercises are free.
Frequently asked questions
How do I work out a weighted average?
=SUMPRODUCT(scores,weights)/SUM(weights).