Sum cells that meet multiple criteria with Excel SUMPRODUCT

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

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.

Click on the cell B7 in the worksheet to make it the active cell;

Enter the following formula in cell B7:=SUMPRODUCT(($A$2:$B$6>25)*($A$2:$B$6<75)*(A2:B6))

The answer 250 should appear in cell B7

The answer was obtained by adding the five digits of the range (40, 45, 50, 55, and 60) ranging from 25 to 75. The total is 250

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