SUMPRODUCT in Excel: Weighted Averages and Criteria
· 7 min read
SUMPRODUCT multiplies two or more ranges row by row, then adds up the results. With weights in B2:B5 and scores in C2:C5:
=SUMPRODUCT(B2:B5, C2:C5)is the same as =B2*C2 + B3*C3 + B4*C4 + B5*C5, without a helper column. That one idea covers the two jobs SUMPRODUCT is used for most: weighted averages, and sums or counts with conditions that SUMIFS cannot express. It works in every version of Excel and does not need Ctrl+Shift+Enter, which is why it shows up in so many older workbooks.
The ranges must all be the same size. If one has 4 rows and another has 5, SUMPRODUCT returns #VALUE!.
Weighted average with SUMPRODUCT
A purchasing team scores two suppliers from 1 to 10 on four criteria. Price matters most, so each criterion has a weight. Columns E and F show the multiplication SUMPRODUCT does for you.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Criterion | Weight | Supplier A | Supplier B | A × weight | B × weight |
| 2 | Price | 40% | 8 | 6 | 3.20 | 2.40 |
| 3 | Quality | 30% | 6 | 9 | 1.80 | 2.70 |
| 4 | Delivery | 20% | 9 | 7 | 1.80 | 1.40 |
| 5 | Service | 10% | 7 | 9 | 0.70 | 0.90 |
| 6 | Weighted score | 100% | 7.50 | 7.40 | 7.50 | 7.40 |
| 7 | Plain average | 7.50 | 7.75 |
The weighted score in C6:
=SUMPRODUCT($B$2:$B$5, C2:C5) / SUM($B$2:$B$5)Supplier A scores 7.50 and Supplier B 7.40. With a plain AVERAGE, B looks better: 7.75 against 7.50. B is strong on quality and service, but those carry only 40% of the weight between them. A is better on price, which carries 40% on its own. Weighting changes the answer, which is exactly why it matters.
Why divide by the sum of the weights?
SUMPRODUCT gives the weighted total. Dividing by the total weight turns it into an average. Here the weights add up to 100% (B6), so dividing changes nothing, but keep the division anyway. If someone later changes the weights to 5, 3, 2 and 1, or the percentages stop adding up to 100%, the score is still on the 1 to 10 scale.
The same pattern answers "average price per unit" or "average delivery time per order" when each row stands for a different number of units or orders. Put the counts where the weights are. Averaging the rows directly gives a small row the same say as a big one, which is the most common averaging mistake in reports. Our average formula guide covers plain AVERAGE, AVERAGEIF and when the median is a better choice.
SUMPRODUCT with multiple criteria
The second use: conditions written as comparisons inside SUMPRODUCT. Here is a returns log with a region, a reason and the refund paid:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Return | Region | Reason | Units | Refund | Question | Result | |
| 2 | R-01 | East | Damaged | 4 | $96.00 | East and Damaged: refund | $211.00 | |
| 3 | R-02 | West | Wrong item | 2 | $30.00 | Same with -- | $211.00 | |
| 4 | R-03 | East | Late | 1 | $18.00 | East and Damaged: count | 3 | |
| 5 | R-04 | North | Damaged | 3 | $54.00 | East or West, Damaged | $356.00 | |
| 6 | R-05 | East | Damaged | 2 | $70.00 | Refund over 20 per unit | $311.00 | |
| 7 | R-06 | West | Damaged | 5 | $110.00 | SUMIFS check | $211.00 | |
| 8 | R-07 | South | Wrong item | 1 | $12.00 | AI's formula | $0.00 | |
| 9 | R-08 | East | Wrong item | 6 | $84.00 | |||
| 10 | R-09 | West | Damaged | 1 | $35.00 | |||
| 11 | R-10 | East | Damaged | 3 | $45.00 |
How the conditions work
=SUMPRODUCT((B2:B11="East") * (C2:C11="Damaged") * E2:E11)Result: 211.00 (R-01, R-05 and R-10). Step by step:
B2:B11="East"compares every row and gives ten TRUE or FALSE values.C2:C11="Damaged"does the same for the reason.- Multiplying them turns TRUE into 1 and FALSE into 0, so a row is 1 only when both are true. That is AND.
- Multiplying by the refund keeps the refund on matching rows and turns every other row into 0.
- SUMPRODUCT adds it all up.
Leave off the last range and you get a count instead: =SUMPRODUCT((B2:B11="East")*(C2:C11="Damaged")) returns 3.
The -- (double negative)
You will also see the conditions written as separate arguments with two minus signs in front:
=SUMPRODUCT(--(B2:B11="East"), --(C2:C11="Damaged"), E2:E11)Same result: 211.00. The first minus turns TRUE into -1 and FALSE into 0, and the second turns -1 back into 1. It is just a short way of converting TRUE/FALSE into numbers. *1 or +0 do the same. Use whichever your team reads most easily; the multiplication style is the easier one to explain.
The double negative is needed only when a condition is its own argument. When you multiply conditions together, as in the first formula, the multiplication already converts them.
OR conditions: add instead of multiply
For "East or West, and Damaged", add the two region tests. A row that matches either one gets 1 (or 2, if it could match both), so wrap the sum in >0 to turn it back into a clean TRUE/FALSE:
=SUMPRODUCT(((B2:B11="East") + (B2:B11="West") > 0) * (C2:C11="Damaged") * E2:E11)Result: 356.00: the three East returns plus R-06 and R-09 from the West. Adding means OR, multiplying means AND. Swapping one for the other is a quiet way to get a much bigger or smaller total than the question asked for, so read each + and * against the question before trusting the number.
Conditions SUMIFS cannot do
SUMIFS only compares a column with a value. SUMPRODUCT can test a calculation on every row. For the refunds that came to more than 20 per unit:
=SUMPRODUCT((E2:E11/D2:D11 > 20) * E2:E11)Result: 311.00 (R-01, R-05, R-06 and R-09). With SUMIFS you would need a helper column for the refund per unit first. SUMPRODUCT can also test things like the month of a date column with MONTH(range)=3, or a part of a text code.
SUMPRODUCT vs SUMIFS
For simple conditions, SUMIFS is the better choice. H7 shows that =SUMIFS(E2:E11, B2:B11, "East", C2:C11, "Damaged") returns the same 211.00. It is easier to read, handles wildcards and dates, and is faster on big sheets.
| SUMIFS | SUMPRODUCT | |
|---|---|---|
| AND conditions | Yes | Yes, with * |
| OR conditions | Two SUMIFS added up | Yes, with + and >0 |
| Conditions on a calculation | Needs a helper column | Yes |
| Multiply two columns, then sum | No | Yes |
| Speed on 100,000+ rows | Fast | Slower |
Reach for SUMPRODUCT when the question involves multiplying columns (quantity × price), a weighted average, or a condition SUMIFS cannot express. For everything else, see SUMIFS with multiple criteria.
Common mistakes
TRUE/FALSE passed as separate arguments without conversion. The AI's formula in H8 looks reasonable:
=SUMPRODUCT((B2:B11="East"), (C2:C11="Damaged"), E2:E11)It returns 0. When the comparisons are separate arguments, SUMPRODUCT treats every non-numeric entry in its arguments, TRUE and FALSE included, as zero, so every product is 0. There is no error. Either multiply the conditions together inside one argument, or put -- in front of each one.
Ranges of different sizes. B2:B11 with E2:E10 returns #VALUE!. Every range must have the same number of rows and columns. A header row included in one range but not another is the usual cause.
Text in a number column. A cell that says "n/a" makes a * multiplication fail with #VALUE!. Passed as a separate argument instead, the same cell is quietly counted as zero, which is worse because nothing tells you. Clean the column, or check it with ISNUMBER.
Whole-column references. SUMPRODUCT(A:A, B:B) multiplies over a million rows and can slow the workbook down noticeably. Use the actual data range, or an Excel table.
Plain AVERAGE where a weighted one was needed. As in the scorecard, averaging rows that stand for different amounts gives the wrong answer with no warning. Ask what each row represents before you average it.
You can practise SUMPRODUCT on real order lines, including an audit where an AI mixed up AND and OR, on the practice page.
FAQ
What does SUMPRODUCT do in Excel?
It multiplies matching cells of two or more ranges and adds up the products. =SUMPRODUCT(B2:B5, C2:C5) equals B2×C2 + B3×C3 + B4×C4 + B5×C5.
How do I calculate a weighted average in Excel?
Use =SUMPRODUCT(weights, values) / SUM(weights). Dividing by the sum of the weights keeps the result on the same scale as the values.
How do I use SUMPRODUCT with multiple criteria?
Multiply the conditions and the values: =SUMPRODUCT((B2:B11="East")*(C2:C11="Damaged")*E2:E11). Each condition gives 1 or 0 per row, so only rows where all are true are added.
What does -- mean in SUMPRODUCT?
It converts TRUE and FALSE into 1 and 0. It is needed when a condition is passed as its own argument, as in =SUMPRODUCT(--(B2:B11="East"), E2:E11).
Why does my SUMPRODUCT return 0?
Most often the conditions are separate arguments without --, so TRUE and FALSE count as zero. Check also that the text in your conditions matches the data exactly.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- The real average price per Coffee & Kitchen unit
- One region, one category, one cell
- West’s online sales look far too bigAudit
- Cedar Studios’ March list value in one cell