Cellwise Try it free

Finance function

Excel SUMPRODUCT function

SUMPRODUCT multiplies matching items in two or more ranges and adds up the results. Use it to total an order from quantities and prices, or to sum with conditions without a helper column.

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

Arguments

array1
The first range or array.
array2
Optional. A second range the same size, multiplied item by item with the first.

SUMPRODUCT examples

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

ABCD
1ItemCategoryQtyUnit price
2NotebookPaper402.5
3Gel pensPens251.2
4Copy paperPaper108.75
5MarkersPens123.4
6StaplerDesk411
  1. =SUMPRODUCT(C2:C6,D2:D6)

    Result: 302.3

    Multiplies each quantity by its price and adds them up: the order total.

  2. =SUMPRODUCT((B2:B6="Paper")*C2:C6*D2:D6)

    Result: 187.5

    Only the Paper rows count, because the test is 1 for a match and 0 otherwise.

  3. =SUMPRODUCT(--(C2:C6>=12))

    Result: 3

    Counts the items ordered in quantities of 12 or more.

  4. =ROUND(SUMPRODUCT(C2:C6,D2:D6)/SUM(C2:C6),2)

    Result: 3.32

    The average price per unit, weighted by quantity.

How to use SUMPRODUCT

  1. Click the cell for the total.
  2. Type =SUMPRODUCT(
  3. Select the first range, type a comma, and select the second.
  4. Close the bracket and press Enter.

Common SUMPRODUCT mistakes

Ranges of different sizes

Every range must have the same number of rows and columns, or SUMPRODUCT returns #VALUE!.

Conditions return TRUE and FALSE, not numbers

Multiply the condition by another range, or put -- in front, so TRUE becomes 1.

Selecting whole columns

C:C makes SUMPRODUCT work through a million rows. Select only the rows you need.

SUMPRODUCT FAQ

How do I multiply two columns and sum the result in Excel?

Use =SUMPRODUCT(C2:C6,D2:D6). It multiplies row by row and adds the products.

How do I calculate a weighted average in Excel?

Divide SUMPRODUCT by the sum of the weights: =SUMPRODUCT(values,weights)/SUM(weights).

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

It turns TRUE and FALSE into 1 and 0 so they can be added up. It is a common way to count matches.

Practice SUMPRODUCT in these lessons

Guides that use SUMPRODUCT

Related functions

Updated