DMW logo
contact@consultdmw.com

How to Use the Microsoft® Excel SUMPRODUCT Function

How to apply Excel’s versatile SUMPRODUCT function on its own or in combination with other functions, and in response to criteria set against one or more cell ranges.

Last updated on 2020-12-03 by David Wallis.


SUMPRODUCT’s Structure and Capabilities

This is what Excel’s Insert Function feature says of SUMPRODUCT: “Returns the sum of the products of corresponding ranges or arrays”. But SUMPRODUCT is a much more versatile function than this description suggests: it’s not limited to the summation of products.

The syntax of SUMPRODUCT as indicated by Excel is this:

SUMPRODUCT(array1,array2,array3arrayN)

In this context an array is a continuous range of cells in a single row or in a single column. For arrays you use in your SUMPRODUCT formula, all of them must contain the same number of cells must be the same in each array. Also all arrays must be in columns or in rows.

Excel SUMPRODUCT dataIn this spreadsheet, arrays are in columns. We could assign two of them — B2:B11 and C2:C11 — to this formula:

=SUMPRODUCT(B2:B11,C2:C11)

The result of the formula would be 770.

What SUMPRODUCT has done is this:

(1×2)+(2×4)+(3×6)+ … + (10×20)

The same result is achieved by this version of the formula, in which an asterisk replaces the comma:

=SUMPRODUCT(B2:B11*C2:C11)

That’s because the sign separating the arrays determines the mathematical operation that SUMPRODUCT undertakes.

Thus this SUMPRODUCT returns the result 165:

SUMPRODUCT(B2:B11+C2:C11) ≡ (1+2)+(2+4)+(3+6)+ … + (10+20)

And this one returns the result 5:

SUMPRODUCT(B2:B11/C2:C11) ≡ (1÷2)+(2÷4)+(3÷6)+ … + (10÷20)

The examples above are based on arrays arranged in columns. SUMPRODUCT works equally well with arrays of values arranged in rows, as in this example:

=SUMPRODUCT(A19:F19*A20:F20)


Applying a Criterion to SUMPRODUCT

You may include a criterion within a SUMPRODUCT formula. For example, consider limiting SUMPRODUCT to the HR department values in this spreadsheet:

SUMPRODUCT critierion

For example, you want to find the result of 252 for HR following this arithmetic:

(1×2)+(3×6)+(4×8)+(6×12)+(8×16)

Your formula to achieve the result should be this:

=SUMPRODUCT((C2:C11*D2:D11)*(B2:B11="HR"))

Take care over the pairings of brackets. I recommend you test your formula with simple values before setting it loose on live data.


SUMPRODUCT With Multiple Criteria

You may apply more than one criterion to SUMPRODUCT. For example, in this spreadsheet you want it to apply only to the HR department’s activity with Client B:

Criteria of Excel SUMPRODUCT formula

That is, you want a result for those values highlighted in colour. Your formula should be this:

=SUMPRODUCT((D2:D11*E2:E11)*(B2:B11="HR")*(C2:C11="Client B"))


SUMPRODUCT Exact Matches

A number of Excel functions will match one text string to another but may not take into account the case of each character in the strings. By combining SUMPRODUCT with EXACT, you can achieve case-sensitive matching as in this example:

SUMPRODUCT EXACT

Note the double minus, --, which is the tweak needed to cause the formula to work as intended. Normally EXACT returns TRUE or FALSE; the -- forces these into becoming 1 or 0, respectively.

You could test this with:

=--EXACT("ABC","ABC")

and

=--EXACT("ABC","Abc")

The SUMPRODUCT functions needs these 1s and 0s, as supplied by the EXACT function.


Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

Don’t be mislead by its name, SUMPRODUCT is about more than just sums and products