Documentation/How Tos/Calc: SUMPRODUCT function

SUMPRODUCT

Returns the sum of the products of corresponding array elements.

Syntax:

SUMPRODUCT(array1; array2; ... array30)

array1 to array30 are up to 30 arrays or ranges of the same size whose corresponding elements are to be multiplied.

SUMPRODUCT returns for the i elements in the arrays.

You can use SUMPRODUCT to calculate the scalar product of two vectors.

Advanced topic:

SUMPRODUCT evaluates each parameter array1 to array30 as an * array formula, but does not need to be entered as an array formula. In other words it can be entered with the Enter key, rather than Cntrl-Shift-Enter. See the example below.

SUMPRODUCT can also be used to sum cells where a specified condition is true - see * Conditional Counting and Summation and the example below.

Example:

SUMPRODUCT(A1:B2; F1:G2)

returns A1*F1 + B1*G1 + A2*F2 + B2*G2.

Advanced topic:

SUMPRODUCT(ABS(A1:A6))

when entered 'normally' (not as an * array formula), returns the sum of the absolute values in cells A1:A6. SUMPRODUCT forces ABS(A1:A6) to be evaluated as an * array formula.

SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6)

returns the sum of cells in C1:C6 whose corresponding entries in the A column are red and in the B column are big. This is not portable to Excel, as Excel ignores logical values.

SUMPRODUCT((A1:A6="red") * (B1:B6="big") * C1:C6)

a version of SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6) which is portable to Excel, as the logical values are converted to numbers during the multiplication.

Issues:

• In Excel SUMPRODUCT ignores * logical values (that is, it treats them as 0). In Calc TRUE is 1 and FALSE is 0.