Way back in 2008, I wrote about using SUMPRODUCT to duplicate the functionality of SUMIFS which was introduced in Microsoft Excel 2007.
SUMPRODUCT is a powerful excel function and is more commonly used to multiply two arrays. Let’s first understand the syntax of SUMPRODUCT.
If you’re impatient, you can skip the tutorial and download the sample excel file from the end of this post. It contains solved examples with the related formulae. We’ll be using the following table in the example.
Array1, array2, array3, … are 2 to 255 arrays whose components you want to multiply and then add.
It is important to remember that SUMPRODUCT needs the arrays to be of the same length, else it gives a #VALUE! error
Example 1 of the excel gives you can example of using SUMPRODUCT to multiply two arrays. We’ve sense checked to see that it works properly by entering the long formula.
Using SUMPRODUCT to divide two arrays
Beyond multiplying, SUMPRODUCT also allows you to divide 2 array as you can see in the examples below. All you need is an array with just 1s of the same length and dimensions as your other arrays. Note the use of “,” and “;” in the array that allows you to define a horizontal or a vertical array. Use them both together to create a multidimensional array.
Do you find this tutorial useful? Do you use SUMPRODUCTS a lot? If you have a suggestion, a tip or a tutorial comment below and let us know.