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:
The sum of the products where the value in column A is greater than zero turns out to be 170.
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 here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Interpolate Missing Values in Excel
How to Find the Top 10 Values in a List in Excel
How to Find the Top 10% of Values in an Excel Column
