Maths and conditional totals

Excel SUMPRODUCT Function: Multiply Then Add, in One Step

Multiplies ranges together row by row, then adds up the results.

SUMPRODUCT multiplies corresponding cells in two or more ranges and totals the results. Units times unit price, down a whole column, in one cell — no helper column of line totals to build and then hide.

That is the everyday use, and it would be a modest function if that were all. Its second life comes from how it treats logical tests: because TRUE behaves as 1 and FALSE as 0, multiplying a range by a condition zeroes out the rows that fail. =SUMPRODUCT((D2:D6="North")*B2:B6) totals the North rows without SUMIF being involved at all.

SUMIFS has made most of that unnecessary, and you should reach for SUMIFS first — it is clearer and faster. SUMPRODUCT keeps its place for the things SUMIFS still cannot do: OR conditions inside a single formula, criteria computed from a function rather than compared to a value, and any workbook that has to open in an ancient version of Excel.

Syntax

=SUMPRODUCT(array1, [array2], …)

Arguments

array1 Required: The first range. With only one argument it behaves exactly like SUM.
array2, … Optional: Further ranges, multiplied element by element with the first. All must be exactly the same shape.

The example data

Headers in row 1, data in A2:D6. Note the blank in C4 and the text in C6.

	A	B	C	D
1	Item	Units	Unit price	Region
2	Cordless drill	12	89.99	North
3	Extension lead	40	12.5	South
4	Safety goggles	8		North
5	Work gloves	25	5.4	South
6	Tool belt	6	n/a	North

Worked examples

=SUMPRODUCT(B2:B3, C2:C3)

Result: 1579.88

12 × 89.99 plus 40 × 12.50. The weighted total, with no line-total column needed.

=SUMPRODUCT((D2:D6="North") * B2:B6)

Result: 26

The condition evaluates to TRUE/FALSE, which multiplies as 1/0, so only North rows survive.

=SUMPRODUCT((D2:D6="North") * (B2:B6>10))

Result: 1

Two conditions multiplied gives a conditional count — the pre-COUNTIFS way of doing it.

=SUMPRODUCT(((D2:D6="North") + (B2:B6>30)) > 0)

Result: 4

OR logic, which COUNTIFS cannot express. Adding conditions gives 2 where both hold, so the >0 flattens it back to a count of rows.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: SUMPRODUCT Function

Common errors and how to fix them

#VALUE!

Why it happens: The ranges are different shapes, or one contains text that ends up inside an arithmetic operation.

How to fix it: Make every array identical in height and width. Where text may appear, multiply by a condition that excludes it rather than including it in the product.

Returns 0 unexpectedly

Why it happens: One of the conditions is never true — often a text comparison defeated by trailing spaces.

How to fix it: Evaluate each bracket on its own first; a condition returning all FALSE zeroes the entire product.

Double-counts with OR conditions

Why it happens: Adding two conditions gives 2 where both are true, and SUMPRODUCT happily totals that 2.

How to fix it: Wrap the sum in a >0 test to flatten it back to 1, as in the fourth example above.

Tips worth knowing

Use * between conditions for AND and + for OR — that is the whole grammar.
Reach for SUMIFS and COUNTIFS first. SUMPRODUCT is for what they cannot express, not a general replacement.
SUMPRODUCT works on whole columns but reads every cell in them, so keep the ranges tight on large files.
Double-unary -- converts TRUE/FALSE to 1/0 where there is nothing to multiply by: =SUMPRODUCT(--(D2:D6="North")).

Frequently asked questions

When should I use SUMPRODUCT instead of SUMIFS?

When you need OR logic in one formula, when the criterion is the result of a function rather than a plain comparison, or when the workbook must open in Excel 2003. For ordinary AND conditions, SUMIFS is clearer and faster and should be the default.

Why does multiplying by a condition work?

Excel treats TRUE as 1 and FALSE as 0 in arithmetic. Multiplying a value by a condition therefore keeps it when the condition holds and zeroes it when it does not, and SUMPRODUCT adds the survivors.

Does SUMPRODUCT need Ctrl+Shift+Enter?

No. It handles arrays natively, which was its main appeal before dynamic arrays — it gave you array behaviour without the special key combination that legacy array formulas required.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions