Table of Contents
SUMPRODUCT is a function in Excel that allows users to multiply corresponding elements in arrays and then return the sum of those products. By adding a condition to the function, such as “>0”, it can be used to only calculate values that are greater than zero. This is useful for situations where only positive values need to be considered in a calculation, allowing for more accurate and efficient data analysis.
Excel: Using SUMPRODUCT Only with Values Greater Than Zero
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 .
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use SUMPRODUCT in Excel to only calculate values that are greater than zero?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-sumproduct-in-excel-to-only-calculate-values-that-are-greater-than-zero/
stats writer. "How can I use SUMPRODUCT in Excel to only calculate values that are greater than zero?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-sumproduct-in-excel-to-only-calculate-values-that-are-greater-than-zero/.
stats writer. "How can I use SUMPRODUCT in Excel to only calculate values that are greater than zero?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-sumproduct-in-excel-to-only-calculate-values-that-are-greater-than-zero/.
stats writer (2024) 'How can I use SUMPRODUCT in Excel to only calculate values that are greater than zero?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-sumproduct-in-excel-to-only-calculate-values-that-are-greater-than-zero/.
[1] stats writer, "How can I use SUMPRODUCT in Excel to only calculate values that are greater than zero?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use SUMPRODUCT in Excel to only calculate values that are greater than zero?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.