The SUMPRODUCT function in Excel is a very versatile function that will give different results depending on the 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 form of the arguments, SUMPRODUCT will count the number of cells in a given range containing data that meets specific 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 count cells is:
= SUMPRODUCT([condition1] * [condition2])
Example: Counting Cells that Meet Multiple Conditions
The following example will find the total number of cells in the data 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 Count:
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 ))
- Press the Enter key on the keyboard
- The answer 5 should appear in cell E6 as there are only five values in the range - 40, 45, 50, 55, and 60 - that are between 25 and 75
Breaking Down the SUMPRODUCT Function
When conditions are set for the 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 and a value of 0 for array elements that are FALSE.
The corresponding ones and zeros in each array are multiplied together:
- with 1 x 1 returning a value of 1
- with 1 x 0 returning a value of 0
These ones and zeros are then summed by the function to give us a count of the number of values that meet both conditions.
Or, think of it this way...
Another way to think of what SUMPRODUCT is doing is to think of the multiplication sign as an AND condition.
With this in mind, it is only when both conditions are met - numbers greater than 25 AND less than 75 - that a TRUE value (which is equal to one remember) is returned.
The function then sums up all the true values to arrive at the result of 5.