Summarize cells that meet multiple criteria with Excel SUMPRODUCT

Content
  1. Sum of cells that fall between two values
  2. SUMPRODUCT Function syntax for summing cells
  3. Example: Summarize data in cells that meet multiple conditions
  4. Entering the SUMPRODUCT function
  5. Destruction of the SUMPRODUCT formula
  6. Multiplying ones and zeros by a range
  7. Sum up

Sum of cells that fall between two values

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

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

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

Since Excel 2007, the program has two functions – SUMIF and SUMIFS – that sum the data in cells that match one or more specified criteria.

However, sometimes SUMPRODUCT is easier to work with when it comes to searching for multiple conditions that belong to the same range, 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:

= SUM PRODUCT([условие1] † [условие2] † [массив]†

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

array – a continuous range of cells

Example: Summarize data in cells that meet multiple conditions

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

Entering the SUMPRODUCT function

Because this example uses an incorrect 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 entered manually in a worksheet cell.

  1. Click on a cell B7 on the worksheet to make it active;

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

  3. Answers 250 should appear in cell B7

  4. The answer was obtained by adding five numbers in the range (40, 45, 50, 55 and 60) that are between 25 and 75. Total 250

Destruction of the SUMPRODUCT formula

When conditions are used as arguments, SUMPRODUCT evaluates each array element according to the condition and returns a boolean value (TRUE or FALSE).

For calculation purposes, Excel assigns a value a for those elements of the array that are TRUE (fulfilling the condition), and the value 0 for array elements evaluated as FALSE (does not match condition).

For example the number 40:

  • TRUE for the first condition, so the value is assigned in the first array a

  • TRUE for the second condition, so the value a assigned in the second array.

no. 15:

  • FALSE for the first condition, so the value 0 assigned in the first array;

  • TRUE for the second condition, so the value a 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 which returns the value 1;

  • For the number 15 – we have 0 x 1, returning the value 0.

Multiplying ones and zeros by a range

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

This is done to give us the numbers added by the function.

This works because:

  • 1 time is a random number equal to the original

  • 0 times any number is 0

So we end up with:

  • 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

Sum up

SUMPRODUCT then sums the above results to find the answer.

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

Leave a Reply

Your email address will not be published.