Table of Contents
The SUMPRODUCT function in Excel can be used to sum the product of two or more arrays of numbers, while excluding any values that are less than zero. To do this, wrap the SUMPRODUCT function in an IF statement that checks each value before it is included in the calculation. This way, only values greater than zero are used in the calculation.
The SUMPRODUCT function in Excel returns the sum of the products of two corresponding arrays.
To use this function only with values that are greater than zero, you can use the following formula:
=SUMPRODUCT(--(A1:A9>0),A1:A9,B1:B9)
This particular formula will only return the sum of the products of the two arrays for the values that are greater than zero in the range A1:A9.
The following example show how to use this formula in practice.
Example: Use SUMPRODUCT Only With Values Greater than Zero
Suppose we have the following two arrays of values in Excel:
If we use the SUMPRODUCT function as usual, we can take the sum of the products between the values in column A and column B:
The sum of the products turns out to be 144.
We can manually verify that this is correct:
Sum of Products = 10*3 + 5*4 + 0*5 + (-3)*2 + 6*5 + 4*5 + 10*2 + (-5)*4 + 10*5 = 144.
To only take the sum of the products where the value in column A is greater than zero, we can use the following formula:
=SUMPRODUCT(--(A1:A9>0),A1:A9,B1:B9)
The following screenshot shows how to use this formula in practice:
We can manually verify that this is correct:
Sum of Products = 10*3 + 5*4 + 6*5 + 4*5 + 10*2 + 10*5 = 170.
Note: You can find the complete documentation for the SUMPRODUCT function .