Show All
SUMPRODUCT
See Also
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 30 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?
- Create a blank workbook or worksheet.
- Select the example in the Help topic. 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 Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
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.