# SUMPRODUCT IF Formula – Excel & Google Sheets

Written by

Reviewed by

Download the example workbook

*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.