SUMPRODUCT in Excel
Last updated
SUMPRODUCT multiplies ranges row by row and adds the results. For example, =SUMPRODUCT(C2:C11,D2:D11) multiplies each quantity in C by its price in D and returns the total revenue, with no helper column. With tests such as (A2:A11="North") it also adds only the rows that meet your conditions.
SUMPRODUCT syntax
=SUMPRODUCT(array1, [array2], [array3], …)
- array1
- The first range of numbers, e.g.
C2:C11. It can also be a calculation on ranges, such as(A2:A11="North")*C2:C11. - array2
- Optional. The range multiplied by array1, row by row, e.g.
D2:D11. It must have the same size as array1. - array3, …
- Optional. More ranges of the same size, up to 255. Each row’s values are multiplied together before the results are added.
Practice SUMPRODUCT here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Total revenue in one formula
In F2, write a SUMPRODUCT that returns the total revenue of the orders in A1:D11: each quantity in column C times its unit price in column D, all added together.
| Region | Product | Quantity | Unit price ($) | Total revenue ($) | |
| North | Notebook | 10 | 5.00 | ||
| East | Backpack | 8 | 35.00 | ||
| South | Pen | 30 | 3.00 | ||
| North | Desk lamp | 5 | 28.00 | ||
| West | Notebook | 20 | 5.00 | ||
| North | Pen | 25 | 3.00 | ||
| South | Desk lamp | 4 | 28.00 | ||
| East | Notebook | 15 | 5.00 | ||
| North | Backpack | 6 | 35.00 | ||
| West | Pen | 40 | 3.00 |
How to use SUMPRODUCT, step by step
- Click the cell where you want the total and type
=SUMPRODUCT(. - Select the first column, for example the quantities, then type a comma.
- Select the second column, for example the unit prices, over exactly the same rows.
- Close the bracket and press Enter. To add a condition, write it in brackets and multiply it in:
=SUMPRODUCT((A2:A11="North")*C2:C11*D2:D11).
SUMPRODUCT examples
Quantity × price
=SUMPRODUCT(C2:C11,D2:D11)
Multiplies each quantity by its unit price and adds the ten results: the total revenue in one cell.
Weighted average
=SUMPRODUCT(C2:C11,D2:D11)/SUM(C2:C11)
Divides the revenue by the number of units sold, so each price counts as many times as it was sold. AVERAGE(D2:D11) would weigh every order the same.
One condition
=SUMPRODUCT((A2:A11="North")*C2:C11*D2:D11)
The test returns TRUE or FALSE on each row. Multiplying turns them into 1 and 0, so only the North rows add to the revenue.
Two conditions
=SUMPRODUCT((A2:A11="North")*(C2:C11>=10)*C2:C11*D2:D11)
Multiplying two tests works as AND: a row counts only when it is in the North and has at least 10 units.
Common SUMPRODUCT problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #VALUE! | The ranges don’t have the same size, e.g. C2:C11 and D2:D10. | Select every range from the same first row to the same last row. |
| 0 with a condition | The test was passed as its own argument, as in =SUMPRODUCT(A2:A11="North",C2:C11,D2:D11). TRUE and FALSE are then counted as 0. | Multiply the test in with *: =SUMPRODUCT((A2:A11="North")*C2:C11*D2:D11). |
| Total too low | Some numbers are stored as text. With comma-separated ranges, SUMPRODUCT counts them as 0. | Convert the column to numbers, or multiply the ranges with * so the text is turned into numbers. |
| Wrong totals after copying | The ranges moved down one row each time the formula was copied. | Lock every range with $ (F4) and leave only the criteria cell relative. |
SUMPRODUCT vs SUMIFS
SUMIFS adds a column that already holds the values you want: =SUMIFS(E2:E11,A2:A11,"North") needs a column of line totals. SUMPRODUCT can multiply as it adds: =SUMPRODUCT((A2:A11="North")*C2:C11*D2:D11) works straight from quantities and prices. Use SUMIFS when the amount column exists, as it is shorter and easier to read. Use SUMPRODUCT when you need to multiply columns, build a weighted average or write a condition SUMIFS can’t express.
Frequently asked questions
How do I add a condition to SUMPRODUCT?
Write the test in brackets and multiply it by the ranges: =SUMPRODUCT((A2:A11="North")*C2:C11*D2:D11). TRUE counts as 1 and FALSE as 0, so rows that fail the test add nothing.
Can SUMPRODUCT use OR logic?
Yes. Add the tests instead of multiplying them, and wrap the sum in a check: =SUMPRODUCT(((A2:A11="North")+(A2:A11="South")>0)*C2:C11*D2:D11).
How do I calculate a weighted average?
Divide SUMPRODUCT by the sum of the weights: =SUMPRODUCT(C2:C11,D2:D11)/SUM(C2:C11) gives the average price paid per unit.
Do I need Ctrl + Shift + Enter for SUMPRODUCT?
No. SUMPRODUCT works with ranges and calculations on ranges on its own, so a plain Enter is enough.
Keep practising
All 5 SUMPRODUCT exercises, with explanations and progress tracking.
Open the SUMPRODUCT practice set