An Overview of SUMPRODUCT Function | Microsoft Excel
The SUMPRODUCT function is one of the math and trigonometry functions in Microsoft Excel. It can be used as a standalone function as well as in collaboration with other functions. This article will give you an overview of the SUMPRODUCT function in Excel.
What is SUMPRODUCT Function?
The SUMPRODUCT function first calculates the multiplication of the first two values. Then it sums up all the multiplications.
Syntax with SUMPRODUCT function,
=SUMPRODUCT(array1, [array2], [array3], ...)Here,
- array1: Compulsory. It refers to the first range of cells whose elements you want to multiply and then sum up the products.
- array2: Non-compulsory. The second cell range to calculate the sum of the product.
- array3: Non-compulsory. The third cell range to calculate the sum of the product.
It will give an output of a numerical value.
Example of SUMPRODUCT Function
Consider the following dataset. It is about 4 fictional items with their corresponding Quantity and Price per Unit. To calculate the Total Price of all the items, first, we need to multiply the quality of each item by its unit price. After that, we need to sum up all the quantities.
For that, we can use the SUMPRODUCT function to do exactly what I just described. For example, the following formula will calculate the total price for all 4 items.
=SUMPRODUCT(B2:B5,C2:C5)Here,
- B2:B5 is array1.
- C2:C5 is array2.
The SUMPRODUCT function first multiplies B2 with C2, then B3 with C3, then B4 with C4, and finally B5 with C5. After that, it sums up all the results. The following breakdown explains it well:
SUMPRODUCT(B2:B5,C2:C5) = (B2*C2) + (B3*C3) + (B4*C4) + (B5*C5)
Steps to Apply the SUMPRODUCT Function
Now, learn to apply the SUMPRODUCT function. To do that, go through the steps below:
- Start with inserting an equal sign (=) in a blank cell.
- Then type SUMPRODUCT.
- Type open parenthesis “(“.
As you type the open parenthesis, Excel shows the syntax of the SUMPRODUCT function.
- Insert a cell address or a range that represents the first argument, array1.
For this instance, I’m inserting range B2:B5 which is the first array in this case. - Then, insert a comma (,).
After inserting the comma, Excel will ask you for the second argument which is array2.
- Now insert another cell address or a range.
Here I’m inserting the range C2:C5. If you have more ranges to calculate, you can insert them all like this. But for this instance, I have only two ranges to insert. So, I’m stopping here.
- Now, add a closing parenthesis “)” to complete the syntax.
- Finally, press ENTER to insert the formula into the cell.
The SUMPRODUCT function will return a numerical value ($75.00) which is the sum of the product of the two ranges specified inside the function.
Availability
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Remarks
- The SUMPRODUCT function allows you to multiply corresponding ranges or arrays and then sum the results.
- The function returns a #VALUE! error if the arrays provided are different in size.
- It can take up to 255 arrays or ranges as arguments.
- It ignores empty cells and cells with non-numeric values.
- The function treats non-numeric values as if they are zeros.
- It can be used with logical values and text. When used with logical values, the function treats TRUE as 1 and FALSE as 0. When used with text, the function treats each character as 1.
- The result of SUMPRODUCT is always a number, even if all the values it multiplies are text values.
Conclusion
The SUMPRODUCT function is one of the math and trigonometry functions in Excel. If you have any questions regarding the SUMPRODUCT function, please comment below. Thanks!
Frequently Asked Questions
Is SUMPRODUCT faster than SUMIFS?
In general, SUMIFS is often faster and more efficient than SUMPRODUCT for conditional summing based on multiple criteria. This is because SUMIFS is specifically designed for this purpose and internally optimized for such operations.
SUMIFS directly handles multiple conditions without the need for array manipulation, providing a more straightforward and optimized approach. It is usually the preferred choice when dealing with large datasets and complex criteria.
On the other hand, SUMPRODUCT involves array multiplication, which may be less efficient for conditional summing compared to SUMIFS. While SUMPRODUCT is a versatile function, it might be slower in scenarios where SUMIFS is optimized for the task at hand.
In summary, if your goal is conditional summing with multiple criteria, and performance is a key consideration, SUMIFS is generally faster and more efficient than SUMPRODUCT. However, the actual performance may depend on the specific dataset and conditions, so it’s recommended to test both methods with your data to determine the optimal approach.
Is SUMPRODUCT an alternative to SUMIFS?
SUMPRODUCT and SUMIFS serve different purposes, but SUMPRODUCT can be used as an alternative to SUMIFS in certain scenarios.
SUMIFS is specifically designed for conditional summing based on multiple criteria. It allows you to sum values that meet specified conditions in different ranges. On the other hand, SUMPRODUCT is a versatile function that can handle array operations, including conditional summing. While not specifically designed for this purpose, you can use SUMPRODUCT for similar tasks.
- SUMIFS:
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …) Designed for conditional summing based on multiple criteria. Explicitly created for this purpose. - SUMPRODUCT as Alternative:
Syntax: =SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)*sum_range) Uses multiplication to simulate AND logic for multiple conditions. Provides an alternative for conditional summing when you want to avoid using SUMIFS.
Consider using SUMIFS when the primary goal is conditional summing, especially when dealing with a large dataset. However, for more complex scenarios or when you want a versatile function, SUMPRODUCT can be a suitable alternative. Choose the method that best fits your specific requirements and the complexity of your data analysis.
Can you use SUMPRODUCT to count?
Yes, you can use the SUMPRODUCT function in Excel to count based on certain criteria. To count the occurrences that meet specific conditions, you can leverage the SUMPRODUCT function along with logical expressions.
Suppose you have a list of numbers in column A, and you want to count how many of them are greater than 5. You can use the following formula: =SUMPRODUCT((A1:A10>5)*1)
In this formula, (A1:A10>5) creates an array of TRUE and FALSE values based on the condition “greater than 5.” Multiplying by 1 converts TRUE to 1 and FALSE to 0.
SUMPRODUCT then adds up these 1s and 0s, giving you the count of numbers that meet the specified condition.
This formula can be adapted for various counting scenarios by adjusting the logical conditions. It provides a flexible way to count based on multiple criteria as well.
For example, to count numbers between 3 and 8, you can use:=SUMPRODUCT((A1:A10>=3)*(A1:A10<=8)*1)
Feel free to customize the conditions based on your specific counting requirements.
How do I use SUMPRODUCT lookup?
SUMPRODUCT can be used for lookups by multiplying corresponding arrays and summing the products. Here’s a simple example of using SUMPRODUCT for lookup:
Suppose you have two columns, A and B, where column A contains the lookup values, and column B contains the corresponding values you want to retrieve. To lookup a specific value from A and return its corresponding value from B, you can use the following formula: =SUMPRODUCT((A1:A10=lookup_value)*(B1:B10))
In this formula, (A1:A10=lookup_value) creates an array of TRUE and FALSE values based on the condition “equal to the lookup value.” Multiplying this array by (B1:B10) effectively filters the corresponding values in column B where the condition is TRUE. SUMPRODUCT then adds up these filtered values, providing the lookup result.
Replace lookup_value with the actual value you want to look up. For example, if you want to find the corresponding value for “Apple” in column A and retrieve the value from column B, the formula would be: =SUMPRODUCT((A1:A10=”Apple”)*(B1:B10))
This formula is versatile and can be adapted for various lookup scenarios. Customize the conditions based on your specific lookup needs.
What is the SUMPRODUCT result 0?
The SUMPRODUCT function in Excel multiplies corresponding components in the given arrays and then sums those products. If the result is 0, it means that there are pairs of corresponding elements that cancel each other out when multiplied.
Mathematically, the SUMPRODUCT formula is expressed as SUMPRODUCT(A1:A3, B1:B3)=A1*B1+A2*B2+A3*B3 If the result is 0, it implies that the sum of these products is zero: A1*B1+A2*B2+A3*B3=0
This could happen if there are pairs of elements in the arrays A1:A3 and B1:B3 that are inversely proportional, meaning one is positive while the other is negative, leading to a net sum of zero. For example: 2×(−3)+(−4)×2+1×6=0
So, if the SUMPRODUCT result is 0, it suggests that there are balancing positive and negative values in the corresponding arrays.
