SUMPRODUCT: Weighted Averages, Two-Condition Sums and the Average Unit Price That Was Out by 516,711.60

The quarterly price review has one number to produce: the average unit price. There is a column of twelve unit prices, and =AVERAGE(E2:E13) returns 104.91.

That number goes into next quarter's forecast, which assumes 6,000 units. Six thousand at 104.91 is 629,475.00.

The gross those same 6,000 units actually produced last quarter was 112,763.40.

The AVERAGE is not broken. It answered the question it was asked, which was "what is the average of these twelve numbers" — and the answer to that is 104.91. Nobody wanted to know that. What they wanted to know was what a unit sells for, and 2,400 cable clips at 4.25 count once in AVERAGE, exactly as heavily as 24 sit-stand desks at 640.00. Weight each price by how many units went out at it and the answer is 18.79.

The formula that produces it is one cell long:

=SUMPRODUCT(D2:D13,E2:E13)/SUM(D2:D13)

SUMPRODUCT is the function people meet once, use for exactly this, and never look at again. It is worth more than that. It multiplies columns without a helper column, it counts on conditions that COUNTIFS cannot express, it ranks on a column that does not exist in the sheet, and it does all of it without Ctrl+Shift+Enter, in every version of Excel there has ever been.

What you need. SUMPRODUCT works in every version of Excel, in Excel for the web, on Mac, and in Google Sheets, and it has never needed Ctrl+Shift+Enter. SUMIFS and COUNTIFS need Excel 2007 or later. Nothing in this article needs Microsoft 365.

1) Two Averages of One Column

🎯 Scenario: The report prints an average unit price. Sales says it is nonsense — nothing on the list sells for a hundred pounds a unit on average. Finance says the formula is =AVERAGE(E2:E13) and points at the column. Both of them are right, which is the whole problem.

Twelve SKUs, and one thing deliberately missing: there is no line-total column. Nobody has multiplied units by price anywhere in this sheet, and this article never will.

Two averages come out of column E:

Both are correct. Only one of them is an answer to "what does a unit sell for", and it is the second. The first is an answer to "what is the average of the numbers in this column", which is a question about the spreadsheet rather than about the business.

The gap is not academic. On a 6,000-unit forecast:

• 6,000 × 104.91 = 629,475.00
• Actual gross on 6,000 units = 112,763.40
• Difference = 516,711.60

Half a million pounds of forecast, produced by a formula with nothing wrong with it.

2) What SUMPRODUCT Actually Does

Strip away the reputation and it is very simple. Given two ranges of the same shape, SUMPRODUCT walks them together, row by row, multiplies each pair, and adds up the results.

=SUMPRODUCT(D2:D13,E2:E13)

returns 112,763.40, and here is the arithmetic it did:

That right-hand column is a helper column. SUMPRODUCT is what you write when you do not want to build it — when the sheet is somebody else's, when there is no free column, when the layout is fixed by whoever exports it, or when you simply do not want a column of intermediate numbers that people can sort out of alignment.

Two things follow immediately, and both matter later:

• The ranges must be the same shape. =SUMPRODUCT(D2:D13,E2:E12) returns #VALUE!, because there is no row 13 partner for row 13. Not a warning, not a partial answer — an error.
• One range is legal. =SUMPRODUCT(D2:D13) is just =SUM(D2:D13), or 6,000. Nothing to multiply by, so it adds.

3) The Weighted Average

A weighted average is the total divided by the total weight, and it is almost always what people mean when they say "average" about anything a business sells.

=SUMPRODUCT(D2:D13,E2:E13)/SUM(D2:D13)

112,763.40 over 6,000 units, or 18.79 a unit.

The pattern is worth memorising as a shape rather than a formula: SUMPRODUCT(weights, values) / SUM(weights). Everything else in this section is that shape with different columns in it.

Split it by category and the mix becomes visible:

Accessories are 91.25% of the units and slightly more than half the money. That is why the blended figure sits at 18.79 rather than anywhere near the midpoint of 10.77 and 102.48 — a weighted average is dragged towards whatever moves in volume, which is exactly the behaviour you want and exactly the behaviour AVERAGE refuses to give you.

Where this bites hardest. Average selling price, average discount, average interest rate across balances, average delivery time across shipments, average defect rate across batches, blended hourly rate across a team. In every one of those, a plain AVERAGE treats a 12-unit line and a 12,000-unit line as equals.

4) Weighting by Something That Is Not Volume

The weights do not have to be units. They can be anything, including numbers you type yourself.

A supplier scorecard rates three dimensions — quality 4.6, delivery 3.1, price 4.9 — and the plain average is 4.20. But the three do not matter equally: quality is 40% of the score, delivery 35%, price 25%. Put the weights in H2:H4 and the scores in I2:I4:

=SUMPRODUCT(H2:H4,I2:I4)

returns 4.15. Because the weights already sum to 1, there is nothing to divide by — and if they did not, you would divide by SUM(H2:H4) and get the same answer, which is a useful property: the weights never have to be normalised by hand.

You can also skip the cells and put the weights inline as an array constant:

=SUMPRODUCT({0.4;0.35;0.25},I2:I4)

Semicolons separate rows in an array constant; commas separate columns. So {0.4;0.35;0.25} is a three-row column that lines up with I2:I4, while {0.4,0.35,0.25} is a three-column row and returns #VALUE! against a vertical range. It is a small thing that costs people twenty minutes the first time.

Hard-coding weights inside a formula is a habit worth resisting anyway — the day the weighting changes, a number in a cell is a five-second edit and a number inside a formula is an archaeology exercise. Use the array constant when the weights are genuinely fixed by definition, and cells the rest of the time.

5) Conditions Are Ones and Zeros

Everything else SUMPRODUCT does comes from one fact: a comparison against a range returns an array.

B2:B13="Accessories" is not a single TRUE or FALSE. It is twelve of them, one per row:

{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}

TRUE and FALSE are not numbers, but arithmetic turns them into numbers: TRUE becomes 1 and FALSE becomes 0 the moment you multiply, add, or subtract. So multiplying that array by the units column zeroes out every row that is not Accessories and leaves the rest intact.

=SUMPRODUCT((B2:B13="Accessories")*D2:D13*E2:E13)

returns 58,962.00 — the Accessories gross. Read the expression left to right and it says: for each row, multiply a 1-or-0 by units by price, then add up the twelve results. The rows that fail the test contribute 0 × units × price, which is 0.

That is the whole trick. Conditions are not a special feature of SUMPRODUCT; they are ordinary multiplication by an array of ones and zeros.

6) Commas or Asterisks — The Rule That Decides

This is the single most common way a SUMPRODUCT goes quietly wrong.

SUMPRODUCT treats anything non-numeric inside its arguments as zero. Text is zero. Blanks are zero. And TRUE and FALSE are non-numeric, so an array of them passed as an argument is an array of zeros.

=SUMPRODUCT((B2:B13="Accessories"),(D2:D13>100))     → 0
=SUMPRODUCT((B2:B13="Accessories")*(D2:D13>100))     → 6

Same test, different answer, no error either time. The first returns 0 and looks like a fact about the data. The second multiplies the two arrays before SUMPRODUCT sees them, so what arrives is an array of ones and zeros, which is numeric.

The rule, stated once:

• Asterisks when the arrays are logical tests. (condition)*(condition)*values
• Commas when the arguments are already numbers. SUMPRODUCT(units, price)
• Mix them freely as long as every logical test is inside a multiplication. =SUMPRODUCT((B2:B13="Furniture")*D2:D13,E2:E13) is fine, and returns 53,801.40.

There is one real difference beyond that. The comma form tolerates text in a numeric range — it reads it as zero and carries on. The asterisk form does not: text × number is #VALUE!. So a range with "n/a" typed in one cell will return an answer with commas and an error with asterisks. Which of those you want depends entirely on whether you would rather be quietly wrong or loudly stopped, and most of the time the answer is loudly stopped.

7) The Double Unary, and What It Is Actually For

You will see this in other people's formulas:

=SUMPRODUCT(--(B2:B13="Accessories"))

Those two minus signs are the double unary. The first negates the array — TRUE becomes −1, FALSE becomes 0, because negation is arithmetic and forces the coercion. The second negates it back, so −1 becomes 1. Net effect: TRUE/FALSE becomes 1/0, and SUMPRODUCT now sees numbers.

It exists for the case where there is nothing to multiply by. If you are counting rows and only have one condition, (B2:B13="Accessories") on its own is an array of logicals, and SUMPRODUCT would return 0. You need to coerce it, and there are three ways:

=SUMPRODUCT(--(B2:B13="Accessories"))      → 6
=SUMPRODUCT((B2:B13="Accessories")*1)      → 6
=SUMPRODUCT((B2:B13="Accessories")+0)      → 6

All three return the six Accessories rows. -- is the traditional one and marginally the fastest; *1 is the one you can explain to a colleague in four seconds. Once there are two or more conditions multiplied together, the multiplication does the coercion for you and the double unary becomes redundant — =SUMPRODUCT(--(B2:B13="Accessories")*--(D2:D13>100)) works, but every one of those minus signs is doing nothing.

8) Counting With SUMPRODUCT

Drop the values and you are counting rows rather than totalling them:

Every one of those has a COUNTIFS equivalent that is shorter and faster, and for questions in this shape you should write the COUNTIFS. The point of the table is the pattern, not the recommendation — sections 11, 12, 13 and 14 are the ones where COUNTIFS runs out and this shape is the only one left.

Note also that all six Accessories lines are over 100 units, so the first two rows agree at 6. That is a fact about this data, not about the formulas — do not read a coincidence as a confirmation.

9) Two Conditions and a Value

Conditions and values compose in one expression, in any order:

=SUMPRODUCT((B2:B13="Accessories")*(C2:C13="North")*D2:D13*E2:E13)

returns 13,920.00, the Accessories gross in North — one line, the laptop stands. Change the category and:

=SUMPRODUCT((B2:B13="Furniture")*(C2:C13="North")*D2:D13*E2:E13)

returns 29,432.40 across three lines, and the two together make the 43,352.40 that North sold in total.

Because it is all multiplication, the order does not matter and neither does the grouping. These three are the same formula:

=SUMPRODUCT((C2:C13="North")*D2:D13*E2:E13)
=SUMPRODUCT(D2:D13*E2:E13*(C2:C13="North"))
=SUMPRODUCT((C2:C13="North")*D2:D13,E2:E13)

All three return 43,352.40. The third one mixes the forms legally: the logical test is inside a multiplication, and what SUMPRODUCT receives as arguments is two numeric arrays.

10) Three Columns at Once

Nothing stops at two columns. The discount in F is a decimal, so 1-F2:F13 is the fraction of the price actually paid:

=SUMPRODUCT(D2:D13,E2:E13,1-F2:F13)

returns 106,683.96 — net revenue, with every line discounted at its own rate, computed from three columns and a constant without a single helper cell.

The discount itself is the same formula with one argument swapped:

=SUMPRODUCT(D2:D13,E2:E13,F2:F13)

returns 6,079.44, and 112,763.40 − 6,079.44 = 106,683.96, which is the arithmetic checking itself.

Two things to notice. First, 1-F2:F13 is an array expression — Excel computes twelve values from it — and SUMPRODUCT handles that natively, with no Ctrl+Shift+Enter, in versions of Excel that predate dynamic arrays by twenty-five years. That is the quiet reason SUMPRODUCT survived: it was array-aware before array formulas were usable.

Second, the weighted average discount is now available the same way:

=SUMPRODUCT(D2:D13,E2:E13,F2:F13)/SUMPRODUCT(D2:D13,E2:E13)

5.39%, against the 5.42% that =AVERAGE(F2:F13) reports. On this data the two nearly agree, which is worth showing precisely because it is the boring case: the discount rates happen not to correlate much with revenue. Change one large line's discount and the two numbers separate immediately. A weighted average and a plain average agreeing is a property of the data on that day, never a reason to stop weighting.

11) OR Conditions, and the Trap of Adding Them

Multiplication is AND: a row survives only if every test is 1. Addition is OR — with a trap in it.

How many lines are Furniture or North? Six are Furniture, four are North, and three are both, so the answer is seven. But:

=SUMPRODUCT((B2:B13="Furniture")+(C2:C13="North"))     → 10

Ten, because the three rows that satisfy both contribute 1 + 1 = 2 each. The addition did not build a union, it built a tally. The fix is to flatten anything above 1 back down to 1:

=SUMPRODUCT(((B2:B13="Furniture")+(C2:C13="North")>0)*1)     → 7

The >0 turns 2 back into TRUE, and *1 coerces. Seven, which is the union.

The same guard is needed whenever an OR sits inside a bigger expression:

=SUMPRODUCT(((C2:C13="North")+(C2:C13="South")>0)*D2:D13*E2:E13)

returns 69,622.40 for North and South together. Here the regions are mutually exclusive so the >0 is not strictly doing anything — but leaving it in costs nothing, and the day somebody adds a row where both tests can be true, the formula is already correct. Write the guard by reflex.

12) Criteria on a Column That Does Not Exist

This is where SUMPRODUCT stops being a slower SUMIFS and starts being the only option.

SUMIFS and COUNTIFS test ranges. They cannot test an expression. So the question "how many lines are worth more than 10,000?" is unanswerable with COUNTIFS, because there is no column of line values to point it at — the line value is units × price, and nobody built that column.

SUMPRODUCT does not care:

=SUMPRODUCT((D2:D13*E2:E13>10000)*1)

5 lines clear 10,000 — the sit-stand desks at 15,360.00, cable trays at 15,000.00, laptop stands at 13,920.00, privacy panels at 12,480.00 and cable clips at 10,200.00. And their combined value:

=SUMPRODUCT((D2:D13*E2:E13>10000)*D2:D13*E2:E13)

66,960.00, or 59.4% of the gross from five of the twelve lines.

The same freedom applies to any expression: (D2:D13*E2:E13*F2:F13>500) finds lines where the discount alone cost more than 500, (E2:E13/MAX(E2:E13)<0.05) finds prices under 5% of the dearest, (LEFT(A2:A13,2)="CC") filters on a SKU prefix. SUMIFS can do none of them, and each one is a helper column you did not have to add.

13) Ranking Without RANK

RANK needs a range. If the thing you want to rank is computed, it has nowhere to point — but a rank is only a count of how many values beat yours, plus one, and counting is something SUMPRODUCT does well.

Where does CC-020 Cable Clips (row 10) sit by line value?

=SUMPRODUCT((D2:D13*E2:E13>D10*E10)*1)+1

5th. Four lines are worth more than its 10,200.00; add one for itself.

The same idea does conditional ranks, which RANK cannot do at all — position within a category rather than overall:

=SUMPRODUCT((B2:B13=B10)*(D2:D13*E2:E13>D10*E10)*1)+1

3rd among Accessories, behind the cable trays and the laptop stands. The condition and the comparison sit side by side in the same expression, which is the whole reason this works.

14) Counting Distinct Values

The classic one-liner, and the one most worth understanding rather than copying:

=SUMPRODUCT(1/COUNTIF(C2:C13,C2:C13))

4 distinct regions. =SUMPRODUCT(1/COUNTIF(B2:B13,B2:B13)) returns 2 categories.

The mechanism is neat. COUNTIF(C2:C13,C2:C13) — the same range as both range and criteria — returns an array of how many times each row's own value appears: North appears 4 times, so all four North rows return 4. Take the reciprocal and each of those rows contributes 0.25. Four rows × 0.25 = 1. Every distinct value, however many rows it occupies, contributes exactly 1, and the sum is the count of distinct values.

One caveat that will bite you: a single blank cell in the range returns #DIV/0!, because COUNTIF counts zero occurrences of a blank and the reciprocal of 0 is an error. On a range that might have gaps:

=SUMPRODUCT((C2:C13<>"")/COUNTIF(C2:C13,C2:C13&""))

The &"" gives COUNTIF something non-blank to count so the denominator is never 0, and the leading test contributes 0 for the blank rows so they add nothing.

If you have Microsoft 365, =COUNTA(UNIQUE(C2:C13)) says the same thing far more legibly, and you should write that instead. The 1/COUNTIF form is for the files that have to open in Excel 2016 — of which there are a great many.

15) SUMPRODUCT or SUMIFS

Both of these return 58,962.00:

=SUMPRODUCT((B2:B13="Accessories")*D2:D13*E2:E13)
=SUMIFS(...)                                    ← cannot be written

That second line is the point. SUMIFS sums a range; there is no range here to sum, only a product of two. Once a line-total column exists, =SUMIFS(G2:G13,B2:B13,"Accessories") does the job and does it faster — so the real question is never "which function" but "is there a column, and should there be one".

Where they overlap, SUMIFS and COUNTIFS win, and it is not close:

• They run on Excel's optimised, multithreaded aggregation path. SUMPRODUCT builds arrays in memory and multiplies element by element.
• They accept a whole-column reference without paying for a million empty rows in the same way.
• They read better to the next person, and a criteria argument can point at a cell so the report is filterable.

So the honest split:

And a note on scale, because SUMPRODUCT is the classic way to make a workbook slow: =SUMPRODUCT((B:B="Accessories")*D:D*E:E) over whole columns reads three million cells to answer a question about twelve rows, and pays that cost on every recalculation. Bound the ranges, or better, put the data in a Table and let Table[Units] grow with it.

16) Common Mistakes

• Reporting AVERAGE of a price, rate or percentage column. Almost always the wrong average, and it never announces itself — 104.91 against 18.79 on this data.
• Commas around a logical test. =SUMPRODUCT((A="x"),(B>1)) returns 0, silently, and 0 looks like an answer.
• Ranges of different lengths. #VALUE!, every time. It happens most when a row is inserted at the bottom of one range and not the other.
• Whole-column references. A:A is 1,048,576 rows, and SUMPRODUCT will faithfully read all of them.
• Adding conditions to mean OR without the >0 guard. Rows that satisfy both get counted twice — 10 instead of 7 here.
• Text in a numeric range with the asterisk form. #VALUE! from one cell containing "n/a" or a hyphen.
• 1/COUNTIF over a range with blanks. #DIV/0!, fixed by COUNTIF(range,range&"").
• Array constants with the wrong separator. {0.4,0.35,0.25} is a row and will not line up with a column.
• Using SUMPRODUCT where SUMIFS fits. Same answer, more cost, harder to read, and it will not accept a criteria cell as gracefully.
• Leaving the weights hard-coded inside the formula. When the weighting changes — and it does — the formula has to be rewritten instead of a cell retyped.

Conclusion

SUMPRODUCT does one thing: it lines up ranges, multiplies across, and adds up. Every use in this article is that sentence with different arguments — units by price for a total, weights by scores for a rating, ones and zeros by values for a conditional sum, a computed expression by itself for a rank.

What makes it worth knowing in 2026, with SUMIFS, FILTER and LET all available, is the narrow band of things nothing else does: weighting, and criteria against a column that only exists inside the formula. Those two cover the weighted average, the conditional rank, the distinct count and the "lines worth more than 10,000" question, and none of them need a helper column, a new version of Excel, or Ctrl+Shift+Enter.

And the number to leave with is the one at the top. The report said the average unit price was 104.91. It was 18.79. Nothing in that sheet was broken, no formula returned an error, and the forecast was out by 516,711.60 — because AVERAGE was asked about a column when the question was about the business.