How to use Sumproduct Formula in Excel?
The Sumproduct formula multiplies the corresponding items and returns the sum of the products. The Sumproduct formula performs two functions one is Product and the other is Sum. The Sumproduct formula combines two functions in a single formula. This tutorial will explain you about the Sumproduct formula in detail.
- What is Sumproduct formula?
- The syntax or the formula of the Sumproduct formula
- Sumproduct formula with multiple criteria
What is Sumproduct Formula?
The Sumproduct formula first multiples the arrays and then add those arrays together. It is a worksheet function & also used for calculating data in the worksheet. This Function is a Math/Trig function and will be found in the function library Formula tab. When you first use this function it may seem boring and complex, but once you start using it you will come to know how versatile the function is.
The syntax of the Sumproduct Formula:
The Syntax or the formula of Sumproduct is
=SUMPRODUCT(array1, [array2], [array3], …)
Where,
array 1, array 2, array3 are the range of cells that you want to multiply.
There must be at least 1 array in the formula and a maximum of 255 arrays can be included in the formula. However, in the earlier version, only 30 arrays were included in the formula.
Points to Remember:
- All arrays must have the same number of rows and columns.
- If the arrays in the formula do not have the same number of rows and columns then it will return #VALUE! Error.
- If there is no numeric value in the array, these values will be treated as 0.
- Sumproduct does not allow wildcard characters.
Sumproduct with Multiple Criteria:
In Microsoft Excel, you can complete a task with other ways as well. But when it comes to comparing arrays especially with multiple criteria Sumproduct is the most effective one. In most cases, you need to convert them to 1 and 0 by using the double unary operator (–).
Suppose you want to compare two column then you will use the Sumproduct formula as =SUMPRODUCT (–(C2: C10<B2: B10))
The range in the formula is just for example. If you want to add one more criterion to the formula you can add by starting with a double unary operator (–).
Conclusion:
This function is a very useful function as it helps to analyze multiple arrays in Excel. This function was introduced in the year 2000. The Sumproduct return numeric value only. It is the most helpful function. You can also combine this function with other function as well. This function is used to handle a large number of data.