SUMPRODUCT

Excel Functions

Multiply, then sum arrays

Syntax

EXCEL

=SUMPRODUCT(array1, [array2], ...)

Arguments

Parameter	Description
`array1`	The first array or range to multiply, then add.
`array2`	[optional] The second array or range to multiply, then add.

Return Value

The result of multiplied and summed arrays

Details

The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 ranges or arrays can be supplied. When you first encounter SUMPRODUCT, it may seem boring, complex, and even pointless. But SUMPRODUCT is an amazingly versatile function with many uses. Because it will handle arrays gracefully, you can use it to process ranges of cells in clever, elegant ways. The "classic" SUMPRODUCT example illustrates how you can calculate a sum directly without a helper column. For example, in the worksheet below, you can use SUMPRODUCT to get the total of all numbers in column F without using column F at all: To perform this calculation, SUMPRODUCT uses values in columns D and E directly like thi

Examples

Classic SUMPRODUCT example

To perform this calculation, SUMPRODUCT uses values in columns D and E directly like this:

EXCEL

=SUMPRODUCT(D5:D14,E5:E14) // returns 1612

Classic SUMPRODUCT example

The result is the same as summing all values in column F. The formula is evaluated like this:

EXCEL

=SUMPRODUCT(D5:D14,E5:E14)
=SUMPRODUCT({10;6;14;9;11;10;8;9;11;10},{15;18;15;16;18;18;15;16;18;16})
=SUMPRODUCT({150;108;210;144;198;180;120;144;198;160})
=1612

SUMPRODUCT for conditional sums

A typical use for the SUMPRODUCT function is to calculate conditional sums, much like you would use a function like SUMIFS. In the worksheet shown bel

EXCEL

=SUMPRODUCT(--(C5:C14="red"),F5:F14) // red
=SUMPRODUCT(--(B5:B14="tx"),--(C5:C14="red"),F5:F14) // tx and red
=SUMPRODUCT(--(B5:B14="co"),--(C5:C14="blue"),F5:F14) // co and blue
=SUMPRODUCT(--(C5:C14<>"red"),F5:F14) // not red

SUMPRODUCT for conditional sums and counts

Using SUMPRODUCT, you can sum total sales for Texas ("TX") with this formula:

EXCEL

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

SUMPRODUCT for conditional sums and counts

You can also count total sales for Texas ("TX") with this formula:

EXCEL

=SUMPRODUCT(--(A2:A6="TX"))

SUMPRODUCT with double negative (--)

Now SUMPRODUCT can perform the calculation successfully. In array terms, the formula evaluates like this:

EXCEL

=SUMPRODUCT({0,0,1,0,1},{75,100,125,125,150})

SUMPRODUCT

Syntax

Arguments

Return Value

Details

Examples

See Also