  >   >   >

# SUMPRODUCT Function

## Returns the sum of the products of corresponding array components.

 by Charley Kyd, MBAMicrosoft Excel MVP, 2005-2014 The Father of Spreadsheet Dashboard Reports

Multiplies corresonding components in the given arrays and returns the sum of those products. Although SUMPRODUCT treats its arguments as arrays, it doesn't need to be array-entered.

Syntax, Method 1

With Method 1, the standard approach, each array is placed in a separate argument:

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

• Array1  Required. The first array you want to multiply and then add.
• Array2,...  Array arguments 2 to 255 whose components you want to multiply and then add.

Syntax, Method 2

With Method 2, at least two arrays—and typically, all arrays—are multipled within one argument:

SUMPRODUCT(array1*[array2]*...)

• Array1  Required. The first array you want to multiply and then add.
• Array2,...  Any number of arrays whose components you want to multiply and then add.

Applies To

Excel 2003 and above

Remarks

• SUMPRODUCT can return results from a closed external workbook.
• 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.
• As shown in Examples 4 and 5 below, if the array arguments are logical tests, Method 1 requires that the TRUE and FALSE values be converted to 1 and 0 respectively.
• SUMPRODUCT generally is more powerful that SUMIFS, but calculates more slowly.
• SUMPRODUCT can't support wildcard characters very easily.

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

The following examples all reference this simple product database.  When you multiply numeric values, both Methods produce the same result. This example fails because the first argument returns logical values (TRUE or FALSE), not numeric values. These examples succeed because adding 0 to a logical argument, or multiplying it by 1, converts TRUE to 1 and FALSE to 0. Doing so converts the results to a numeric argument that SUMPRODUCT can multiply. This example succeeds with Method 2 because multiplying the arrays automatically converts the TRUE and FALSE values to 1 and 0. Using Method 2 allows you to mix logical and numeric arrays easily. You can mix Method 1 and Method 2 as long as each argument returns numeric values. Unlike the SUMIFS and COUNTIFS functions, which perform somewhat similar tasks, SUMPRODUCT can rely on additional worksheet functions. Here, for example…

• Example 9 counts the number of Products with five characters in its name.
• Example 10 finds the total Units for all Regions with "n" in their names.
• Example 11 finds the total Units for all Product names beginning with "H".

Other Help   