The SUMPRODUCT function in Excel is a very versatile function that returns different results depending on how the function arguments are entered.

As the name suggests, SUMPRODUCT typically multiplies the elements of one or more arrays to get their product, and then adds or sums the products.

However, by customizing the function’s syntax, it can be used to sum only data in cells that meet certain criteria.

Starting with Excel 2007, the program includes two functions – SUMIFS and SUMIFS – that allow to sum data in cells that meet one or more defined criteria.

However, sometimes it is easier to work with the SUMPRODUCT when trying to find multiple conditions related to the same area, as shown in the image above.

## SUMPRODUCT Function syntax for summing cells

Contents

The syntax used to make SUMPRODUCT sum the data in cells that meet certain conditions is:

= SUMPRODUCT([condition1] * [condition2] * [array])

condition1, condition2 – the conditions that must be met before the function finds the product of the array.

Array – a series of contiguous cells

## Example: Summarizing data into cells that meet multiple conditions

The example in the image above adds the data in cells in the range D1 to E6, which are between 25 and 75.

## Enter the SUMPRODUCT function

Because this example uses an irregular form of the SUMPRODUCT function, the function dialog box cannot be used to enter the function and its arguments. Instead, the function must be manually entered into a cell on the worksheet.

**B7**in the worksheet to make it the active cell;

**=SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))**

**250**should appear in cell B7

## Break down the SUMPRODUCTS formula

If conditions are used for its arguments, SUMPRODUCT evaluates each array element against the condition and returns a Boolean value (TRUE or FALSE).

For calculation purposes, Excel assigns a value of ** 1 ** for array elements that are TRUE (meets the condition) and a value of ** 0 ** for array elements that are FALSE (does not satisfy the condition).

For example the number 40:

- for the first condition is TRUE, i.e. a value of
**1**is assigned in the first table; - for the second condition is TRUE, i.e. a value of
**1**is assigned in the second table.

Number 15:

- is FALSE for the first condition, i.e. a value of
**0**is assigned in the first table; - for the second condition is TRUE, i.e. a value of
**1**is assigned in the second table.

The corresponding ones and zeros in each array are multiplied together:

- For the number 40, we have 1 x 1, which gives a value of 1;
- For the number 15, we have 0 x 1, which gives a value of 0.

## Multiplication of ones and zeros by the scale

**These ones and zeros are then multiplied by the numbers in the interval A2:B6.**

This allows us to know the digits that will be added by the function.

It works because:

- 1 times any number is equal to the starting number
- 0 times any number is 0

So we end up at:

- 1 * 40 = 40

0 * 15 = 0

0 * 22 = 0

1 * 45 = 45

1 * 50 = 50

1 * 55 = 55

0 * 25 = 0

0 * 75 = 0

1 * 60 = 60

0 * 100 = 0

## summary of results

SUMPRODUCT then summarizes the above results to find the answer.

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250