SUMPRODUCT Overview
The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the way the function's arguments entered.
Normally, what the SUMPRODUCT function does is multiply the elements of one or more arrays and then add or sum the products together.
But by adjusting the function's syntax, it can be used to sum only the data in cells that meets specific criteria.
Since Excel 2007, there also has been available the SUMIF and SUMIFS functions that will allow you to 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 example below.
SUMPRODUCT Function Syntax to Count Cells
The syntax used in this example to get SUMPRODUCT to sum data in cells that meet specific conditions is:
= SUMPRODUCT([condition1] * [condition2] * [range])
Example: Summing Data in Cells that Meet Multiple Conditions
The following example will add up the data in cells in the range D1 to E6 that are between 25 and 75.
Entering the Data
Enter the data in the table below into cells D1 to D6 of an Excel worksheet as can be seen in the image above.
40 15
22 45
50 55
25 75
60 100
Sum:
Entering the SUMPRODUCT Function
Because this example uses an irregular form of the SUMPRODUCT function, we cannot use the function's dialog box to enter the function and its arguments. Instead, the function will be typed into cell E6.
- Click on cell E6 in the worksheet - the location where the function results will be displayed
- Type the following formula into cell E6 of the worksheet:
= SUMPRODUCT(( D1:E5 > 25 )*( D1:E5 < 70 ) * ( D1:E5 ))
- Press the Enter key on the keyboard
- The answer 250 should appear in cell E6
- 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 D1 : D6.
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 number
- 0 times any number is equal to 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
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
