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		10
		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	West	50	18
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.