﻿ SUMPRODUCT function

# SUMPRODUCT function

Multiplies corresponding components in the given arrays, and returns the sum of those products.

Syntax

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

Array1, array2, array3, ...     are 2 to 255 arrays whose components you want to multiply and then add.

Remarks

• The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

• SUMPRODUCT treats array entries that are not numeric as if they were zeros.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.

2. Select the example in the Help topic.

Note   Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.

4. In the worksheet, select cell A1, and press CTRL+V.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 1 2 3 4
 A B C D Array 1 Array 1 Array 2 Array 2 3 4 2 7 8 6 6 7 1 9 5 3 Formula Description (Result) =SUMPRODUCT(A2:B4, C2:D4) Multiplies all the components of the two arrays and then adds the products — that is, 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. (156)

Remark

The preceding example returns the same result as the formula SUM(A2:B4*C2:D4) entered as an array. Using arrays provides a more general solution for doing operations similar to SUMPRODUCT. For example, you can calculate the sum of the squares of the elements in A2:B4 by using the formula =SUM(A2:B4^2) and pressing CTRL+SHIFT+ENTER.

Applies To: Excel 2007

﻿