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
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
78 * 84 = |
6,552 |
79 * 83 = |
6,557 |
. |
|
. |
|
. |
|
70 * 100 = |
7,000 |
77,578 |
Count by criteria
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:
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.
Advanced examples:
- How to SUM 2d ranges with SUMPRODUCT
- How to calculate weighted average with SUMPRODUCT
- Using SUMPRODUCT to Do Your Taxes
- Case Sensitive Count
- More…
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.