Free Excel Help – SumProduct Function

The Microsoft Excel application is one of the global leaders when it comes to financial computing and analyzing data, from an Excel consultant and financial analyst point of view at least. This iconic piece of software, ever since it was released by Microsoft, has been a the first choice among all the major business organizations in the world for their day to day computing purposes. Microsoft Excel has a massive list of functions at its disposal which makes the lives of all the Excel consultants, financial analysts, and accounts, etc., not just a tad bit easier but spares them from computing even trivial sums or products. One of these functions is the SUMPRODUCT function and its primary purpose is to find and multiply elements of two or more arrays and add them up to get the results. Here in this free Excel help article we will provide a brief description of the function.

The primary purpose of the Microsoft Excel SUMPRODUCT function is to find and multiply the values present in the fields of the arrays specified by the user and then add them up to get the desired results. So, it first jots down the elements that are permissible for the operation and then the actual multiplication of the corresponding parts occurs. After this the values after the multiplication are added to give the final result. It is that simple! The function can take a wide range of arguments and therefore it is not constant for it to have one or two parameters to be certain. So, usually it is written as SUMPRODUCT (array1, array2…arrayn). Here the array fields are specified for taking the arrays that are to be taken into consideration for the multiplication process and there are ranges that are to be mentioned in order to keep a tab on the elements to be considered for the whole operation. So it is taken care of in that way. Also it has to be kept in mind that the arrays and their fields have to be exactly the same for the multiplication process to work, otherwise it will result in extra or deficient number of elements in any one array and thus will result in an error.

Let us take a look at an example for the whole process and examine how it works. Consider the following input SUMPRODUCT ({1,2,3;3,4,5}, {3,4,5;7,9,3}). There are two arrays mentioned here and both are two dimensional in nature i.e. both have the same dimensions. So, how will SUMPRODUCT compute for these two arrays? Well, here how it will: (1*3) + (2*4) + (3*5) + (3*7) + (4*9) + (5*3) which will result to: 3 + 8 + 15 + 21 + 36 + 15 = 98. So, the result 98 is the sum-product for the following inputs. Not only numerical inputs are permissible as parameters for the SUMPRODUCT function, but also array names and numbers are allowed. If we explicitly make an array of elements and make another one with the same dimensions (or maybe not) and then ask the function to compute on equal parts of them then it will work precisely. For example, SUMPRODUCT (B1:B11, C1:C11) will efficiently and easily compute the sum-product for the array elements of B and C within the range of 1 to 11 and then add them up.

Even SUMPRODUCT is powerful enough to include criterions for operations into it as well. These are very easy to implement and the user doesn’t need to learn any extra syntax as well for doing them. For example if the user wants to perform sum-product on the array fields whose corresponding fields have the word “HTTP” affixed to them, then he has to call the function as SUMPRODUCT ((C1:C11*B1:B11)*(D1:D11 = “HTTP”)) and the permissible elements will be operated on only. Even multiple criterions can be passed to the function as in SUMPRODUCT ((B1:B11*C1:C11)*(D1:D11=”HTTP”)*(E1:E11=”HR”)) and here it will both look for the words “HTTP” and “HR” in the D and E columns respectively and only work on the fields corresponding to them.

The MS Excel SUMPRODUCT function can take from 2 to 255 arrays as its inputs for computation but each one of these arrays have to be of the same dimensions otherwise Excel will return the #VALUE! Error. Also if the arrays have any non-numeric quantity in the specified fields then they will be taken up as 0 and then the computation will presume on them. The Excell SUMPRODUCT function is compatible with Excel 2000, 2003, 2004, 2007, 2008, 2010 and 2011 versions and is widely used by Excel consultants and financial analysts throughout the world .