SUMPRODUCT IF Formula – Excel & Google Sheets

This tutorial demonstrates how to calculate the “sumproduct if” of a range, returning the sum of the products of arrays or ranges based on criteria.

SUMPRODUCT Function

The SUMPRODUCT Function is used to multiply arrays of numbers, summing the resultant array.

To create a SUMPRODUCT-IF formula, use the SUMPRODUCT Function along with the IF Function in an array formula.

SUMPRODUCT IF

By combining SUMPRODUCT and IF in an array formula, you essentially create a SUMPRODUCT-IF that works similar to how the built-in SUMIF Function works. Let’s walk through an example.

Start with a list of sales by manager in different regions with corresponding commission rates:

Suppose you wany to calculate the commission amount for each manager like so:

To accomplish this, you can nest an IF Function with manager as the criteria inside of the SUMPRODUCT Function like so:

=SUMPRODUCT(IF(<criteria range>=<criteria>,<values range1>*<values range2>))

=SUMPRODUCT(IF($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))

When using Excel 2019 and earlier, you must enter the formula by pressing CTRL + SHIFT + ENTER to get the curly brackets around the formula (see top image).

How does the formula work?

The formula works by evaluating each cell in the criteria range as TRUE or FALSE.

Calculate the total commission for Olivia:

=SUMPRODUCT(IF($C$2:$C$10=$G2,$D$2:$D$10*$E$2:$E$10))

=SUMPRODUCT (IF({TRUE; TRUE;FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {928.62; 668.22;919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))

Next, the IF Function replaces each value with FALSE if its condition is not met.

= SUMPRODUCT({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})

Now the SUMPRODUCT Function skips the FALSE values and sums the remaining values (2,077.40).

SUMPRODUCT IF With Multiple Criteria

To use SUMPRODUCT-IF with multiple criteria (similar to how the built-in SUMIFS Function works), simply nest more IF statements into the SUMPRODUCT-IF formula like so:

=SUMPRODUCT(IF(<criteria1 range>=<criteria1>, IF(<criteria2 range>=<criteria2>, <values range1>*<values range2>))

=SUMPRODUCT(IF($B$2:$B$10=$G2,IF($C$2:$C$10=$H2,$D$2:$D$10*$E$2:$E$10)))

Then hit CTRL + SHIFT + ENTER.

Another Approach to SUMPRODUCT IF

Often in Excel, there are multiple ways to derive to the desired results. A different way to calculate the “sumproduct if” is to include the criteria within the SUMPRODUCT Function as an array using double unary like so:

=SUMPRODUCT(--($B$2:$B$10=$G2),--($C$2:$C$10=$H2),$D$2:$D$10*$E$2:$E$10)

This method uses the double unary (–) to convert a TRUE/FALSE array to zeros and ones. SUMPRODUCT then multiplies the converted criteria arrays together:

=SUMPRODUCT({1;1;0;0;0;1;0;0;0},{1;0;1;0;1;0;0;0;0},{928.62; 668.22;919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})

Tips and Tricks:

• Where possible, always lock-reference (F4) your ranges and formula inputs to allow auto-filling.
• If you are using Excel 2019 or newer, you may enter the formula without CTRL + SHIFT + ENTER.

SUMPRODUCT IF in Google Sheets

SUMPRODUCT-IF works exactly the same in Google Sheets as in Excel, except that you must use the ARRAYFORMULA Function instead of CTRL + SHIFT + ENTER to create the array formula.