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 the other functions. This article will give you an overview of the SUMPRODUCT function in Excel.
Objectives
The SUMPRODUCT function first calculates the multiplication of the first two values. Then it sums up all the multiplications.
Syntax
=SUMPRODUCT(array1, [array2], [array3], ...)Arguments
- 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.
Output
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)
Usage Guide of SUMPRODUCT Function
Step_1: Start with inserting an equal sign (=) in a blank cell.
Step_2: Then type SUMPRODUCT.
Step_3: Type open parenthesis “(“.
As you type the open parenthesis, Excel shows the syntax of the SUMPRODUCT function.
Step_4: 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.
Step_5: Then insert a comma (,).
After inserting the comma, Excel will ask you for the second argument which is array2.
Step_6: 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.
Step_7: Now, add a closing parenthesis “)” to complete the syntax.
Step_10: 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!
