Math

SUMPRODUCT

Definition

SUMPRODUCT returns one number after multiplying values that sit in the same position in two arrays, then adding those products.

Syntax

=SUMPRODUCT(array1, array2)

Arguments

Argument	Meaning
`array1`	The first list of numbers, such as `B2:B5`.
`array2`	The second list, paired with array1 by position.

Worked example

A workshop lists hours and hourly rates for four workers. Hours are in B2:B5 and rates are in C2:C5.

A: Worker	B: Hours	C: Rate
Ana	3	12
Ben	5	12
Cam	2	15
Dee	4	10

Formula: =SUMPRODUCT(B2:B5,C2:C5)

Result: 166. 3 times 12, 5 times 12, 2 times 15, and 4 times 10 are 36, 60, 30, and 40, which add to 166.

Practice

Cells A2:A5 hold 2, 3, 4, and 1. Cells B2:B5 hold 5, 6, 7, and 8. In C2, write a formula that multiplies each A value by the B value in the same row and adds those products.

In C2, write a SUMPRODUCT formula for A2:A5 and B2:B5.
C2 should show the sum of the products of A2:A5 and B2:B5.

Step 1 of 2

Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.

Loading the sheet…

Related functions

SUM adds a single list of numbers. SUMIF adds numbers that pass a test. SUMPRODUCT multiplies paired cells first, then adds those products.

Common mistake

Giving the two arrays different lengths, so the formula returns #VALUE! instead of a total.