SUMPRODUCT()

Syntax. SUMPRODUCT(array1,array2,array3,...)

Definition. This function multiplies the matching components of the indicated arrays and returns the sum of these products.

Arguments

• array1 and array2 (required) and array3 (optional) At least two and up to 255 components of arrays (30 in Excel 2003 and earlier versions) that you want to multiply and add.

Background. For a sum product, the values in an array are multiplied and the results are added. The formula is:

a₁ b₁

a₂ b₂ = (a₁ · b₁ + a₂ · b₂ + a₃ · b₃)

a₃ b₃

For example, you can calculate the total price of the products listed in a table.

The array arguments must have the same number of rows and columns. If they do not, the SUMPRODUCT() function returns the #VALUE! error. SUMPRODUCT() treats array entries that are not numeric as if they were zeros.

Example. Suppose you want to calculate the total price for the following products:

• 12 apples at $0.39

• 15 pears at $0.42

The result is calculated as follows:

Total price = 12 • 0.39 + 15 • 0.42 = 10.98

In Excel, the initial values are entered in two arrays (see Figure 16-34).

Figure 16-34. Arrays containing the amount and price.

Enter the following formula in the formula bar:

=SUMPRODUCT(B2:B3,C2:C3)

See Also

MMULT(), PRODUCT(), SUM()

Sample Files

Use the SUMPRODUCT worksheet in the Chapter16.xls or Chapter16.xlsx sample file. The sample files are found in the Chapter16 folder. For more information about the sample files, see the section titled Using the Sample Files.