Cellwise Try it free

Excel guide · 3 min read

How to use SUMPRODUCT in Excel

Quick answerSUMPRODUCT multiplies ranges row by row and adds up the results. =SUMPRODUCT(B2:B8,C2:C8) multiplies units by price on each row and returns the total revenue. Add conditions in brackets, like =SUMPRODUCT(B2:B8*C2:C8*(D2:D8="East")), to total only the rows that match. It works in every version of Excel.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1ProductUnitsPriceRegion
2Standing desk12249East
3Office chair3089West
4Desk lamp4524.5East
5Bookshelf8129North
6Monitor arm2659West
7Footrest4019.5East
8Filing cabinet5179North

Download this sheet as an Excel practice file (.xlsx)

The basic use: multiply two columns and add

Without SUMPRODUCT you would add a Revenue column (=B2*C2, filled down) and then SUM it. SUMPRODUCT does both steps in one cell: 12 x 249, plus 30 x 89, and so on for all seven rows.

Separate the ranges with commas. Every range must be the same size, or you get #VALUE!.

=SUMPRODUCT(B2:B8,C2:C8)

Result: 11001.5

11,001.50 total revenue across all seven products.

Add a condition

A comparison like (D2:D8="East") gives TRUE or FALSE per row. Multiplying by it turns TRUE into 1 and FALSE into 0, so rows from other regions drop out of the total.

Note the switch from commas to *. With commas, SUMPRODUCT ignores TRUE and FALSE values, so a bare condition does nothing. Either multiply everything with *, or turn the condition into numbers with --, as in =SUMPRODUCT(B2:B8,C2:C8,--(D2:D8="East")).

=SUMPRODUCT(B2:B8*C2:C8*(D2:D8="East"))

Result: 4870.5

4,870.50 revenue from the East (desks, lamps and footrests).

Count rows with conditions

Leave out the value column and SUMPRODUCT counts the rows where every condition is 1. This one counts West or North products that sold at least 10 units.

For OR, add the tests and wrap the sum in >0. Without that, a row that matched both tests would count twice.

=SUMPRODUCT(((D2:D8="West")+(D2:D8="North")>0)*(B2:B8>=10))

Result: 2

2: the office chair (30) and the monitor arm (26). Both North products sold fewer than 10.

Weighted average

The average price per product is misleading when volumes differ. Weighting each price by the units sold gives the average price customers actually paid: total revenue divided by total units.

=SUMPRODUCT(B2:B8,C2:C8)/SUM(B2:B8)

Result: 66.274096386

About 66.27 per unit. A plain AVERAGE(C2:C8) says 107, because it gives the 5 filing cabinets the same weight as the 45 lamps.

Tests that SUMIFS and COUNTIFS cannot do

SUMIFS only checks cells against fixed criteria. SUMPRODUCT can test a calculation on each row, like units x price, LEN, MONTH or SEARCH. Here: how many product lines brought in more than 1,000.

Which to pick: SUMIFS and COUNTIFS are faster and easier to read for plain conditions on a column, so use them first. Reach for SUMPRODUCT for row-by-row maths, OR logic, or when you need the formula to work in older Excel without Ctrl+Shift+Enter.

=SUMPRODUCT(--(B2:B8*C2:C8>1000))

Result: 5

5 product lines made over 1,000. The desk lamp (1,102.50) just makes it; the filing cabinet (895) does not.

Common mistakes

Ranges of different sizes

SUMPRODUCT(B2:B8,C2:C7) returns #VALUE!. Every range must cover the same rows, and the same columns if you use 2D ranges.

A condition with a comma that is ignored

SUMPRODUCT(B2:B8,(D2:D8="East")) treats TRUE and FALSE as zero and returns 0. Use * or -- to turn the condition into 1s and 0s.

Text in a range you multiply

Multiplying with * makes any text cell, including a header, a #VALUE! error. Start ranges below the header, and convert numbers stored as text.

Whole columns slow the workbook

SUMPRODUCT(B:B*C:C) does over a million multiplications on every recalculation. Use exact ranges or an Excel table.

FAQ

What does the double minus (--) do in SUMPRODUCT?

It turns TRUE and FALSE into 1 and 0. The first minus makes them -1 and 0, the second flips them back. Multiplying by 1 or adding 0 does the same thing.

Is SUMPRODUCT better than SUMIFS?

For simple conditions, no: SUMIFS is faster and easier to read. SUMPRODUCT wins when the condition needs a calculation on each row, OR logic across columns, or an array that SUMIFS does not accept.

Can SUMPRODUCT sum with criteria in another sheet?

Yes. Point the ranges at the other sheet, for example =SUMPRODUCT(Sales!B2:B8*Sales!C2:C8*(Sales!D2:D8="East")). The ranges still need the same size.

Does SUMPRODUCT need Ctrl+Shift+Enter?

No. SUMPRODUCT handles arrays natively in every version of Excel, which is why it was the go-to array formula before Excel 365.

Functions used

Related guides

Updated