Multiplies corresponding components in the given arrays, and returns the sum of those products.
Array1, array2, array3, ... are 2 to 255 arrays whose components you want to multiply and then add.
- 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.
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- 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.
||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)
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.