Sheets AcademySheets Academy
Premium
Excel Function Reference

SUMPRODUCT

Multiplies ranges row by row and adds up the results: an order total from quantity and price in one formula, without a helper column.

SUMPRODUCT(array1, [array2], ...)
  • [ ]optional part
array1
The first range, for example quantities in B2:B6.
array2
A second range of the same size, for example prices. You can add a third one and more.
Optional

Return value

A number: the sum of the products of items in the same positions. If the arrays differ in size, you get a #VALUE! error.

Examples

Order total

fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
Product
Qty
Price, $
Order total, $
Copy paper
10
390
Pens
50
25
Folders
20
60
Stapler
2
450
Paper clips
5
45
Sheet1
E2 shows the whole order total at once: $7,475. Change the number of pens, and the total recalculates.

Revenue for one city

fx
A
B
C
D
E
F
1
2
3
4
5
6
7
8
9
City
Qty
Price, $
Boston revenue, $
Boston
12
1500
Denver
8
1500
Boston
5
2300
Austin
10
900
Boston
3
900
Denver
6
2300
Sheet1
E2 shows revenue for Boston only: 12·1500 + 5·2300 + 3·900 = $32,200. Replace “Boston” in the formula with “Denver”.

Details

SUMPRODUCT takes the first cells of all the ranges and multiplies them, then the second cells, and so on to the end, and then adds up all the products. =SUMPRODUCT(B2:B6,C2:C6) is B2*C2 + B3*C3 + … + B6*C6.

All ranges must be the same size, otherwise you get a #VALUE! error.

You can put a condition in parentheses inside: (A2:A7="Boston") gives 1 in matching rows and 0 in the rest. Multiplying by it zeroes out the other rows, so they don’t get into the sum.

Common mistakes

  • Ranges of different lengths, for example B2:B6 and C2:C7: the formula returns #VALUE!.
  • In the comma form, text in a range counts as zero. In the multiplication form, text gives #VALUE!, so don’t include headers in the range.
  • A condition without parentheses or condition text without quotes: Excel won’t understand (A2:A7=Boston).

See also

SUMSUMIFSSUMIF