SUMPRODUCT

Description

Use this function to multiply ranges together and return the sum of the products. Supported in Chains.

SUMPRODUCT(range_1, […, range_254])

This function accepts the following arguments:

Name	Required	Description	Valid input
`range_1`	Yes	The first range of cells to be multiplied and then totaled. If no other range is provided, then the values are multiplied by 1.	A cell, a cell range, or a formula which results in a cell range.
`range_n`	No	The subsequent range(s) of cells to be multiplied and then totaled. Up to 254 additional ranges can be included.	A cell reference (including a range), a number, or a formula which results in either of these.

Use case Formula Explanation and Result

Calculate the sum of the products of corresponding numbers in two ranges.

=SUMPRODUCT(D2:D16, E2:E16)

This formula works in the following manner:

SUMPRODUCT(: This is the function itself.
D2:D16: The first array (Quantity).
E2:E16: The second array (Cost).
The function multiplies the corresponding values from each array (D2*E2, D3*E3, etc.) and then sums those products.

For this data set, the formula returns 1604.81.

Get the sum of a single range of numbers.

=SUMPRODUCT(D2:D16)

This formula works in the following manner:

SUMPRODUCT(: This is the function itself.
D2:D16: When given a single array, SUMPRODUCT behaves just like the SUM function.
The formula sums all values in the range D2:D16.

For this data set, the formula returns 37.

SUMPRODUCT supports up to 255 ranges.
SUMPRODUCT treats non-numeric items (including text, errors, and logical values) as zeros.
All ranges must be the same size or SUMPRODUCT will return a #VALUE! error.

For best performance, SUMPRODUCT should not be used with full column (A:A) references.