For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Math and trigonometry

SUMPRODUCT Function

Returns the sum of the products of corresponding array components.


by Charley Kyd, MBA
Microsoft 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.

Managers...
Charley Kyd can personally help you to use SUMPRODUCT and other functions in your own organization.

Click here to learn more.
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.

Data for the examples illustrating Excel's SUMPRODUCT function

Excel's SUMPRODUCT example 1
When you multiply numeric values, both Methods produce the same result.

Excel's SUMPRODUCT example 2
This example fails because the first argument returns logical values (TRUE or FALSE), not numeric values.

Excel's SUMPRODUCT example 3
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.

Excel's SUMPRODUCT example 4
This example succeeds with Method 2 because multiplying the arrays automatically converts the TRUE and FALSE values to 1 and 0.

Excel's SUMPRODUCT example 5
Using Method 2 allows you to mix logical and numeric arrays easily.

Excel's SUMPRODUCT example 6
You can mix Method 1 and Method 2 as long as each argument returns numeric values.

Excel's SUMPRODUCT example 7
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 ExcelUser Information About SUMPRODUCT

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards