SUMPRODUCT in Excel: Multiply and Add in One Step

SUMPRODUCT in Excel multiplies the items in two or more ranges row by row, then adds up all the results. It turns "quantity times price for every row, then total it" into one formula, with no helper column. This guide is for anyone who already knows SUM and wants the next step: you will use SUMPRODUCT for totals, for conditional sums and counts, and learn the two errors that catch most people.

What does SUMPRODUCT do in Excel?#

SUMPRODUCT multiplies each pair of matching values and returns the sum of those products. "Product" here is the maths word for the result of a multiplication.

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

With one range, SUMPRODUCT simply adds it up, like SUM. With two or more, it multiplies the first item of each range, then the second items, and so on, and adds all those results. It works in every version of Excel, on Windows, Mac and the web, and you never need Ctrl+Shift+Enter.

How to use SUMPRODUCT: total revenue in one formula#

The classic use is revenue from a quantity column and a price column. Here is a small order list:

Without SUMPRODUCT, you would add a column E with =C2*D2, fill it down, then use =SUM(E2:E7). With SUMPRODUCT it is one cell:

=SUMPRODUCT(C2:C7,D2:D7)

Excel works out 12×4.5 + 3×24 + 5×8 + 20×4.5 + 2×149 + 4×24 = 54 + 72 + 40 + 90 + 298 + 96. The result is 650.

If you are new to writing formulas, our guide to writing your first Excel formula covers the basics of =, cell references and Enter.

How to add a condition to SUMPRODUCT#

You add a condition by putting a test in brackets and multiplying it with the other ranges. A test such as B2:B7="North" gives TRUE or FALSE for each row. When you multiply, Excel turns TRUE into 1 and FALSE into 0, so rows that fail the test become zero.

=SUMPRODUCT((B2:B7="North")*C2:C7*D2:D7)

This returns 488, the revenue from North only (54 + 40 + 298 + 96). Two conditions work the same way:

=SUMPRODUCT((B2:B7="North")*(A2:A7="Desk lamp")*C2:C7)

That returns 4, the number of desk lamps sold in the North. Each extra test in brackets is another AND condition: a row counts only if every test is TRUE.

SUMPRODUCT can also test values that SUMIFS can't, because it accepts calculations on the range. For example, =SUMPRODUCT((LEFT(A2:A7,4)="Desk")*C2:C7) adds the quantity of every product whose name starts with "Desk", returning 7.

How to count with SUMPRODUCT#

To count rows that pass a test, convert the TRUE/FALSE results into numbers and add them up. The double minus -- is the usual way: the first minus turns TRUE into -1, and the second turns it back into 1.

=SUMPRODUCT(--(B2:B7="North"))

This returns 4, because four rows are North. For two conditions, multiply the tests: =SUMPRODUCT((B2:B7="North")*(C2:C7>=5)) counts North rows with 5 or more units and returns 2.

For plain counts, COUNTIFS is shorter. SUMPRODUCT is useful when COUNTIFS can't express the test, such as OR across different columns. See SUMIFS with OR logic for that pattern.

SUMPRODUCT vs SUMIFS: which should you use?#

Use SUMIFS for straightforward conditional sums, and SUMPRODUCT when you need to multiply or calculate inside the condition.

SUMIFS can't multiply two columns, so "revenue by region" needs either a helper column or SUMPRODUCT. The same multiply-and-add idea gives you a weighted average in Excel, which is one of the most common SUMPRODUCT tasks in job tests.

Why does SUMPRODUCT return #VALUE! or a wrong total?#

Most SUMPRODUCT problems come from range sizes, text in the ranges, or TRUE/FALSE values that were never converted.

1. Ranges of different sizes. =SUMPRODUCT(C2:C7,D2:D6) returns #VALUE! because C2:C7 has six rows and D2:D6 has five. Every range must have the same number of rows and columns.
2. A header row inside a multiplied range. =SUMPRODUCT(C1:C7*D1:D7) returns #VALUE!, because "Qty"*"Price" is text times text. With commas instead, =SUMPRODUCT(C1:C7,D1:D7) treats text as zero and still returns 650, but it is safer to start the ranges below the headers.
3. TRUE/FALSE passed as its own argument. In Excel, =SUMPRODUCT((B2:B7="North"),C2:C7) returns 0, because SUMPRODUCT treats logical values in a separate argument as zero. Write --(B2:B7="North") or multiply the test with * instead.
4. Numbers stored as text. A quantity typed as text counts as zero with commas, or causes #VALUE! with *. Look for the small green triangle in the cell.

Where SUMPRODUCT shows up at work#

SUMPRODUCT is a favourite in practical tests because it checks whether you understand ranges, not just one function. Typical tasks include order value from quantity and unit price, payroll cost from hours and hourly rate, and weighted scores in a performance review. Office and data roles use it often: see the Excel skills for admin jobs and the Excel skills for HR jobs for where it fits.

Two related tools are worth knowing next. MOD and INT for remainders and whole numbers pair well with SUMPRODUCT for "every nth row" tricks, and the SUMIFS function guide has the full reference for conditional sums.

Practise it#

Try the live exercise on this page: one SUMPRODUCT formula for total revenue. Then practise SUM and AutoSum in a live spreadsheet to make range selection automatic, and work through the COUNTIFS and SUMIFS lesson so you know when a conditional sum doesn't need SUMPRODUCT at all.