Excel Professor

The other day I was approached by two of my co-workers who had a problem. They needed to sum the total of their worksheet based on the background of the cell color. Well I said no problem; we can identify the cell color with some visual basic.

With that their eyes glazed over. Too complex for them.

Okay, I only had a few minutes left in the day so I came up with a quick dirty work around.

I first sorted their data by the background color of the cell.

From the Ribbon I chose Data > Sort. I sorted by Column A and my Sort On criteria was Cell Color. I knew they have 5 colors so I added a level of my sort by for each color. This then allowed me to organize their data by background color.

Next I added a helper column next to the data that was highlighted.

I started at the top and Typed Yellow in the first helper cell. I then copied that down so that Yellow appeared next to each highlighted yellow field. I repeated this for Blue, Red, Green, and Orange (there’s no accounting for such an awful color pallet).

Well with my helper column filled in, I just used a simple =SUMIFS calculation based on the value in my helper column.

=SUMIFS(A:A,B:B,"Blue")

=SUMIFS(A:A,B:B,"Yellow")

=SUMIFS(A:A,B:B,"Red")

=SUMIFS(A:A,B:B,"Green")

=SUMIFS(A:A,B:B,"Orange")

The result was a quick work around to get the answers to my co-workers problem.

A	B
1	Yellow
3	Yellow
5	Yellow
7	Yellow
9	Yellow
2	Blue
4	Blue
6	Blue
8	Blue

	Sumifs Total	Formula
Blue Total	20	=SUMIFS(A:A,B:B,"Blue")
Yellow Total	25	=SUMIFS(A:A,B:B,"Yellow")

So once again I was reminded that sometimes the most eloquent solution (in this case visual basic) is not necessarily the best solution for my clients.

How would you have handled this situation?

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:

A list of numbers such as 5, 10, 15, 20.
A range of cells such as C4:C6
A named range defined in the Name Managers such as “Sales”
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.

Sum based on cell background color

SumProduct