Sum Cells that Fall Between Two Values

The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the way the function’s arguments are entered.

Normally, as its name suggests, SUMPRODUCT multiplies the elements of one or more arrays to get their product and then adds or sums the products together.

By adjusting the function’s syntax, however, it can be used to sum only the data in cells that meets specific criteria.

Since Excel 2007, the program has contained two functions - SUMIF and SUMIFS - that will sum data in cells that meet one or more set criteria.

At times, however, SUMPRODUCT is easier to work with when it comes to finding multiple conditions relating to the same range as is shown in the image above.

SUMPRODUCT Function Syntax to Sum Cells

The syntax used to get SUMPRODUCT to sum data in cells that meet specific conditions is:

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

 condition1, condition2 - the conditions that must be met before the function will find the product of the array.

array - a contiguous range of cells

Example: Summing Data in Cells that Meet Multiple Conditions

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

Entering the SUMPRODUCT Function

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

  • Click on cell B7 in the worksheet to make it the active cell;
  • Enter the following formula into 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 arrived at by adding the five numbers in the range (40, 45, 50, 55, and 60) that are between 25 and 75. The total of which is 250

Breaking Down the SUMPRODUCT Formula

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

For the purposes of calculations, Excel assigns a value of 1 for those array elements that are TRUE (meet the condition) and a value of 0 for array elements that are FALSE (do not meet the condition).

For example, the number 40:

  • is TRUE for the first condition so a value of 1 is assigned in the first array;is TRUE for the second condition so a value of 1 is assigned in the second array.

The number 15:

  • is FALSE for the first condition so a value of 0 is assigned in the first array;is TRUE for the second condition so a value of 1 is assigned in the second array.

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

  • For the number 40 - we have 1 x 1 returning a value of 1;For the number 15 - we have 0 x 1 returning a value of 0.

Multiplying the Ones and Zeros by the Range

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

This is done to give us the numbers that will be summed by the function.

This works because:

  • 1 times any number is equal to the original number0 times any number is equal to 0

So we end up with:

  • 1 * 40 = 400 * 15 = 00 * 22 = 01 * 45 = 451 * 50 = 501 * 55 = 550 * 25 = 00 * 75 = 01 * 60 = 600 * 100 = 0

Summing the Results

SUMPRODUCT then sums up the above results to find the answer.

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

Get the Latest Tech News Delivered Every Day