### SumProduct

I was reading online from chan do a large explanation of SumProduct on his blog the other week. I looked at it and thought that’s great, but not quite broken down for the average user. So I spent some time at lunch and wrote it in a way that I thought would be more applicable to the majority of Excel users.

I hope you like it.

Sumproduct
Excels help defines Sumproduct as: SUMPRODUCT(array1, [array2], [array3], …)

Array1  Required.
The first array argument whose components you want to multiply and then add.

Array 2, Array 3,… Optional.
Array arguments 2 to 255 whose components you want to multiply and then add.

So what’s an array? An array can be:
1. A list of numbers such as 5, 10, 15, 20.
2. A range of cells such as C4:C6
3. A named range defined in the Name Managers such as “Sales”
4. A Named Formula: Range

 Description Formula An Array 50 =SUMPRODUCT({5;10;15;20}) A Range 20 30 60 =SUMPRODUCT(C4:C6) A Named Range 60 =SUMPRODUCT(MyRange1) where MyRange1 references cells C4:C6 A Named Formula 60 =SUMPRODUCT(Range) where Range is defined in the Name Manager

So what’s the big deal and why should you learn to love Sumproduct? After all you can always use helper cells with addition and multiplication to accomplish the same total.

Well “sum” is only half of the name, the second part is “product”.

Remember the syntax of Sumproduct,
SUMPRODUCT(array1, [array2], [array3], …)

Only Array 1 is required, Array 2, Array 3 etc are optional, that’s what the square brackets [ ] mean.

Let’s look at the following example where I would like to know the sum of sales for my office and regions. Without SumProduct, in order to get this answer I first would add in Column F a Sales calculation and populate the cells with (Column C x Column D) to get my Sales totals. I then sum all my values in Column F to get my answer of 5,350. Well that takes 6 different calculations. I can replace all of that with just one SumProduct function.

 B C D E F G 3 Office Region Qty Price Sales Formula 4 LA West 50 18 900 =D4*E4 5 Portland North 75 19 1,425 =D5*E5 6 New York East 80 22 1,760 =D6*E6 7 Orlando South 25 17 425 =D7*E7 8 Canada Import 60 14 840 =D8*E8 5,350 =SUM(F4:F8) 5,350 =SUMPRODUCT(D4:D8,E4:E8)
So what is Sumproduct doing? Let’s take a look…
 Array 1 Array 2 Product 50 x 18 = 900 75 x 19 = 1,425 80 x 22 = 1,760 25 x 17 = 425 60 x 14 = 840

My formula is =SUMPRODUCT(D4:D8,E4:E8) and has two arrays

Array 1: D4:D8
Array 2: E4:E8

Sumproduct will multiply each Array together
50 x 18
75 x 19
80 x 22
25 x 17
60 x 14

The resulting values are the products of the two Arrays in my formula.

Finally the Products are added together and the correct answer is returned (5,350).

So Sumproduct is the Sum of the Products of the Arrays

No big deal you say? Doesn’t seem to be worth your while? Well SumProduct is scale able allowing up to 255 arrays. Here is where it really shines.

We can use that to our advantage and build logic into the arrays, allowing us to optionally include some array elements and leave out others. By including an Array where the elements within the Array that we don’t want to Sum are Zero and the Elements within the array that we do want to Sum are 1 we can control what is included in the final Summation.

Let’s say we only want to include the Sales from our North Region

One way to do this is to purely delete the other entries. But what if our worksheet is thousands of rows?
 Office Region Qty Price Sales LA Portland North 75 19 1,425 New York East 80 22 Orlando South 25 17 Canada Import 60 14

What we need to do is add some logic to our equation, effectively accomplishing the following:
 Array 1 Array 2 Array 3 Product 50 x 18 x FALSE = 0 75 x 19 x TRUE = 1,425 80 x 22 x FALSE = 0 25 x 17 x FALSE = 0 60 x 14 x FALSE = 0 Sum 1,425
Time to bring in the Sumproduct
In cell A10 type: North
In cell A11 type: =SUMPRODUCT(D4:D8,(E4:E8)*(C4:C8=A10))
Excel will display 1,425

The addition of a value in cell A10 is simple addition allows us to change the sum based on the value in cell A10.