Function: SUMPRODUCT

by | Mar 28, 2019 | Excel Formulas

sumproduct excel

The SUMPRODUCT Excel formula is a Math & Trig function that multiplies the values in arrays, and returns the sum of products. Although this sounds like something you could do using a combination of the SUM and the PRODUCT formulas, there is more to it! Thanks to its unique ability of working with arrays, the SUMPRODUCT function can be used to compare arrays, sum or count by multiple criteria, calculate the weighted average, and perform other similar functions. In this guide, we’re going to show you how to use the SUMPRODUCT Excel function and also go over some tips and error handling methods.

Supported versions

  • All Excel versions

SUMPRODUCT Excel Syntax

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

Arguments

Array1

The first array or range you want to multiply and add.

[Array2], [Array2=3], …

Optional. The first array or range you want to multiply and add.


Examples

Note that we've used named ranges in formulas for our examples. This is only to make the formulas easier to read and is not required.

Basic use case

=SUMPRODUCT(HP,Attack)
formula is a common use case example. The function multiplies each row from the arrays and sums the products.

78 * 84 =

6,552

79 * 83 =

6,557

.

  

.

  

.

  

70 * 100 =

7,000
 

77,578

Count by criteria

=SUMPRODUCT(--(Generation<>"I"))
formula is an example of how you can use the SUMPRODUCT function to return the count of cells that meet a condition in an array . In our example, the formula returns the number of cells that are not equal to "I" inside the array named Generation .

The double minus (--) operator turns a Boolean value into 1 or 0, which can be aggregated by the SUMPRODUCT.

Although, the SUMPRODUCT is capable to returning a cell count, we recommend using the COUNTIFS function for these type of operations instead.

Sum by criteria

The SUMPRODUCT function can sum the values of cells that meet certain criteria. All you need to do is multiple the value array with conditional arrays. In the following formula, the value of the named range HP is multiplied by conditional named ranges Type and Generation:

=SUMPRODUCT(HP*(Type="WATER")*(Generation<>"I"))

We recommend using the SUMIFS function instead to add values by certain criteria.

Multiple criteria with logical OR

Functions like SUMIFS, COUNTIFS or AVERAGEIFS can take multiple criteria to aggregate results. However, these formulas only work with the logical AND operator. This means that they perform the calculations only for  the cells that meet all conditions.

=SUMPRODUCT(HP*((Type="WATER")+(Generation<>"I")))
formula sums the cells that meet either one of two conditions from the named range HP. The plus operator (+) acts as the logical OR. You can use the product operator (*) for logical AND. For more information, please see: How to sum values with OR operator using SUMPRODUCT with multiple criteria

Download Workbook

Advanced examples:

Tips

  • The SUMPRODUCT function does not require you to press the Ctrl + Shift + Enter combination (entering as an array formula) to evaluate arrays.
  • The maximum number of arrays is 255 in Excel 2016, Excel 2013, Excel 2010, and Excel 2007. The limit is 30 in earlier Excel versions.
  • The SUMPRODUCT evaluates non-numeric fields in arrays as zeroes.
  • Logical tests return Boolean values - TRUE or FALSE. You can convert these results into 1 and 0 using the double minus operator (--) or the N function.

Issues

#VALUE!

If array arguments are not the same size you will get a #VALUE! error.