How to Use SUMPRODUCT in Excel: Multiply, Sum, and Count with Conditions in One Cell

Most Excel functions do one obvious thing. SUMPRODUCT looks like it does one obvious thing too, multiply some numbers and add them up, and then, once you learn its secret, quietly turns into one of the most versatile tools in the whole application. It can total an invoice, compute a weighted average, sum with two or three conditions, count matching rows, and even do OR logic, all without a single helper column and without the old array-formula keystroke.

The reason so few beginners use it is that nobody explains the secret: inside SUMPRODUCT, TRUE and FALSE turn into 1 and 0, and you can do arithmetic with them. That one idea unlocks everything clever the function can do. This guide starts from the plain multiply-and-add version, then walks up to the party tricks, each with a picture, and all built on a single, coherent example you can rebuild yourself.

FAQ — What does this article answer?

Q: What does the SUMPRODUCT function do?

A: At its simplest, SUMPRODUCT takes two or more columns of the same height, multiplies them together row by row, and adds up all the results, returning one number. =SUMPRODUCT(Bags, Price) gives you total revenue without a helper column. Its deeper power is doing conditional sums and counts by multiplying in TRUE/FALSE tests.

Q: Why use SUMPRODUCT instead of a helper column and SUM?

A: Because it does the multiplying invisibly, inside one cell. There is no extra column to build, store, protect, or accidentally overwrite, and the formula keeps working as your data grows. For a one-off it barely matters; for a report you maintain, it is much cleaner.

Q: How does SUMPRODUCT do conditional sums and counts?

A: A test like (Region="Berlin") produces a column of TRUE/FALSE values. Multiply that by your numbers and the TRUEs become 1 (keep the row) and the FALSEs become 0 (erase the row). Multiply two tests together and you get AND. Drop the value column entirely and you are counting instead of summing.

Q: What is the difference between * and a comma inside SUMPRODUCT, and why do people write --?

A: When you join arrays with *, the multiplication forces TRUE/FALSE into 1/0 automatically. When you separate them with commas, SUMPRODUCT does not convert logical values, it treats TRUE/FALSE as 0. So either multiply the tests together, or coerce them yourself with a double minus -- (or *1).

Q: Is SUMPRODUCT still needed in Excel 365 with dynamic arrays?

A: It is less essential than it once was, in Excel 365 a plain =SUM((A2:A7="Berlin")*C2:C7) now spills and works without Ctrl+Shift+Enter, and SUMIFS/COUNTIFS cover many everyday cases. But SUMPRODUCT still wins for OR logic in a single cell, for tests based on a calculation, for weighted sums, and for guaranteed compatibility across every Excel version back to the dark ages.

Q: Does SUMPRODUCT slow down a workbook?

A: It can, if you feed it whole-column references like A:A across hundreds of thousands of rows, because it evaluates every cell. Point it at the rows you actually have (or a Table), and it is perfectly fast for normal business data.

The core idea: a zip fastener for two columns

Picture a zip fastener. Two rows of teeth come together, and the slider meshes them one pair at a time, all the way up. SUMPRODUCT works exactly like that. It walks down two (or more) columns in lockstep, and at each row it multiplies the values sitting side by side. Then it adds every one of those products into a single total.

That is the whole engine. Two columns of {2, 5, 3} and {10, 4, 20} become the pairs 2×10, 5×4, 3×20 — that is {20, 20, 60} — and SUMPRODUCT adds them to 100. In a cell:

=SUMPRODUCT(Bags, Price)

The syntax

=SUMPRODUCT(array1, [array2], [array3], ...)

Each array is a range or a calculation that produces a column (or a grid) of numbers. SUMPRODUCT multiplies the arrays together element by element, then sums the lot. There is only one hard rule, and beginners must not skip it: every array must be the same size, the same number of rows (and columns). If one range runs from row 2 to 7 and another from row 2 to 8, Excel can't line the teeth up and returns the #VALUE! error.

If you give SUMPRODUCT only one array, it simply adds it up, behaving like SUM. The magic starts at two.

The one-cell trick: no more helper columns

Before SUMPRODUCT, the classic way to total "quantity times price" was to build a helper column — =Bags*Price on every row — and then SUM that column. It works, but now you own an extra column forever: it has to be built, kept tidy, and protected from a stray edit.

SUMPRODUCT collapses both steps into one cell. Same answer, nothing extra on the sheet, and it stretches automatically when you add rows. That alone would earn it a place in your toolkit. But we are only warming up.

Meet the example: an artisan coffee roastery

Everything from here uses one small order book from a fictional roastery that sells wholesale bags of coffee. Six orders, columns A to E, data in rows 2 to 7:

We will squeeze six different answers out of these six rows.

Feat 1: Total revenue in one cell

Revenue is bags times price, summed over every order:

=SUMPRODUCT(C2:C7, D2:D7)

That is 12×9 + 8×8 + 20×8 + 15×9 + 10×9 + 6×10 = 108 + 64 + 160 + 135 + 90 + 60 = €617. No helper column, one cell.

Feat 2: A weighted average that tells the truth

Here is where SUMPRODUCT starts to feel clever. What is the average customer rating? The naïve answer averages the rating column: (5+4+4+3+5+4) / 6 = 4.17. But that treats a 6-bag order and a 20-bag order as equally important, which is not how a business thinks. A weighted average weights each rating by the number of bags:

=SUMPRODUCT(C2:C7, E2:E7) / SUM(C2:C7)

The SUMPRODUCT part multiplies bags by rating and totals it: 12×5 + 8×4 + 20×4 + 15×3 + 10×5 + 6×4 = 291. Divide by the 71 total bags and you get 4.10, lower than the plain 4.17, because that big 20-bag Filter order (rated only 4) deserves more pull. This "sum of value×weight, divided by sum of weights" is the canonical weighted-average pattern, and SUMPRODUCT expresses it in one clean line.

The secret that unlocks everything: TRUE/FALSE become 1/0

Now the real magic. Type a comparison across a range and Excel produces a column of logical values. (A2:A7="Berlin") evaluates to {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE} for our roastery. On its own that is just yes/no. But the moment you do arithmetic with it, Excel converts each TRUE to 1 and each FALSE to 0.

That column of 1s and 0s is a mask. Multiply your numbers by the mask and the 1s keep their rows while the 0s wipe theirs out. That is the entire basis of every conditional trick below.

One caution the picture flags, because it trips up almost everyone: if you hand raw TRUE/FALSE to SUMPRODUCT as a comma-separated argument — =SUMPRODUCT((A2:A7="Berlin"), C2:C7) — it quietly treats the logicals as 0 and returns 0. You must either multiply the test in with *, or coerce it yourself with a double minus --. More on that shortly.

Feat 3: Conditional revenue with one test

Total revenue for Berlin only:

=SUMPRODUCT((A2:A7="Berlin") * C2:C7 * D2:D7)

The (A2:A7="Berlin") mask is {1;0;1;0;1;0}. Multiply it through and the München and Hamburg rows collapse to zero, leaving 108 + 160 + 90 = €358. Notice this is a single argument, everything is multiplied together inside one set of brackets, so the * does the coercing for us.

Feat 4: Two tests, joined with AND

Revenue for Berlin and Espresso? Multiply in a second mask:

=SUMPRODUCT((A2:A7="Berlin") * (B2:B7="Espresso") * C2:C7 * D2:D7)

Each row now carries two masks. Multiply them together and a row survives only if both are 1, because 1×1=1, but 1×0 and 0×0 are both 0. A single 0 anywhere on a row erases it. Only the two Berlin-Espresso orders remain: 108 + 90 = €198. This is why * behaves like AND. Add a third test the same way for three conditions, a fourth for four, and so on.

Feat 5: Counting is just summing the mask

Here is the elegant part. To count the Berlin-Espresso orders instead of summing their revenue, keep the masks and drop the value columns:

=SUMPRODUCT((A2:A7="Berlin") * (B2:B7="Espresso"))

With nothing to multiply but 1s and 0s, SUMPRODUCT simply adds up the 1s and there are 2. Same filter, one small change, and a conditional sum becomes a conditional count. (This is exactly what COUNTIFS does; SUMPRODUCT just lets you build it by hand, which pays off when the conditions get exotic.)

If you prefer to keep tests as separate comma arguments, coerce each one with a double minus so they become numbers first:

=SUMPRODUCT(--(A2:A7="Berlin"), --(B2:B7="Espresso"))

-- is simply "negate, then negate again": it flips TRUE to −1 to 1, and FALSE to 0 to 0, a two-character way to turn logicals into numbers.

Going further: OR logic and 2-D grids

OR uses addition instead of multiplication. Revenue from Berlin or München:

=SUMPRODUCT(((A2:A7="Berlin") + (A2:A7="München")) * C2:C7 * D2:D7)

Where multiplication gave AND, addition gives OR: a row scores 1 if either test is TRUE. That is 358 (Berlin) + 124 (München) = €482. One caution, because you are adding masks, a row that satisfies both conditions would score 2 and be double-counted. When the two tests are on the same column (a region can't be Berlin and München at once) you are safe; when they might overlap, rethink the logic.

SUMPRODUCT also handles two-dimensional grids. Point it at a whole rectangular block, say a rate table with regions down the side and products across the top, multiply it by a matching block of quantities, and it multiplies and sums the entire matrix in one cell. That is a genuinely advanced move, but it comes from the very same rule: line up equal-sized arrays, multiply, add.

Your quick-reference cookbook

Keep this card nearby until the patterns are muscle memory.

The * versus comma rule, once and for all

This is the single most common source of confusion, so here it is plainly:

Join your arrays with * and wrap the whole thing in one set of brackets =SUMPRODUCT((test1) * (test2) * values) and the multiplication coerces every TRUE/FALSE to 1/0 for you. This is the style most people use because it reads naturally as "test AND test, times the numbers".

Separate your arrays with commas =SUMPRODUCT(array1, array2) and SUMPRODUCT multiplies them but does not coerce logicals; any TRUE/FALSE array is read as zeros. So with commas you must pre-convert each test using -- or *1. Both styles are correct; just don't mix a comma with an un-coerced test and expect a number other than 0.

Common mistakes and how to fix them

Mismatched range sizes. A #VALUE! error almost always means one of your arrays is a different height from the others. Make every range span exactly the same rows. Using an Excel Table (below) prevents this entirely.

Raw TRUE/FALSE with commas returning 0. If a conditional SUMPRODUCT returns 0 when you expect a number, you have probably passed a test as a comma argument without coercing it. Switch to the * style, or wrap the test in --.

Whole-column references on big data. A:A forces SUMPRODUCT to chew through a million rows and can make a workbook sluggish. Reference the actual data range, or a Table column, instead.

Forgetting brackets around a test. Each comparison needs its own brackets: (A2:A7="Berlin"). Without them, operator precedence produces nonsense.

Text that looks like numbers. If a total comes out too low, the "numbers" may be text pasted from an export. Text "9" will not multiply as the number 9. Clean the column first.

Make it robust with Excel Tables

Hard-coded ranges like C2:C7 go stale the moment you add an order. Put the data in a Table (select it and press Ctrl+T) and use its column names:

=SUMPRODUCT((Orders[Region]="Berlin") * (Orders[Roast]="Espresso") * Orders[Bags] * Orders[Price])

Now Orders[Bags] always means the whole Bags column, however many rows the Table grows to. New orders are included automatically, the ranges can never fall out of sync, and the formula reads almost like a sentence.

When to reach for SUMPRODUCT and when not to

Reach for SUMIFS / COUNTIFS for everyday "sum/count where these columns match these values". They are faster, they read clearly, and they happily take whole-column references. If your job is a straight multi-condition sum with AND logic, they are usually the better first choice.

Reach for SUMPRODUCT when you need something they can't express in a single cell: OR logic, a condition based on a calculation rather than a plain column, a weighted sum or average, or a two-dimensional grid. And when a file must run in old Excel versions with no dynamic arrays, SUMPRODUCT is the dependable classic.

Reach for Power Pivot and DAX when the data outgrows a sheet, many tables, hundreds of thousands of rows, the same report rebuilt every month. A measure like SUMX or SUM in a data model will outrun and outlast a wall of SUMPRODUCT formulas.

Summary

SUMPRODUCT rewards one mental picture: a zip fastener meshing columns tooth by tooth, multiplying each pair, adding the lot with TRUE/FALSE quietly turning into 1/0 so a test can switch rows on and off. Hold that image and the "advanced" formulas stop being advanced. They are all the same trick, dressed differently.

What to read next

This guide pairs naturally with a few other SafeOffice articles. The TRUE/FALSE guide is the theory behind the whole boolean-arithmetic trick. The SUMIFS guide covers the everyday conditional-sum workhorse that SUMPRODUCT complements. The Tables guide keeps your ranges from going stale, and the Power Pivot guide is where to go when the data outgrows a worksheet.

See: Boolean logic in Excel: the magic of TRUE and FALSE

See: How to Use SUMIFS to Sum with Multiple Criteria in Excel

See: Excel Tables: why every dataset should be one

See: Power Pivot and the data model: a beginner's guide