SUMPRODUCT

Quick reference

• 00:04 This function is one of my favorites in Excel.
• 00:07 It's a very powerful function.
• 00:09 In fact, so much so, many years ago one of the first articles I ever wrote for
• 00:14 accounting magazines around the world.
• 00:16 Actually suggested that you could actually build a financial model
• 00:19 using nothing other than SUMPRODUCT.
• 00:22 Don't do that, it will be a terrible model if you did.
• 00:26 But it is such a powerful function and I'll be honest, one of my favorites.
• 00:30 If you're not familiar with the SUMPRODUCT function, let me explain it to you.
• 00:35 Essentially what it does, is it takes two vectors, it can do more than that but
• 00:39 what it will do is multiply the corresponding components in each of
• 00:43 them together and then add them up.
• 00:46 If your eyes are glazed over and you're not quite sure what I'm taking about,
• 00:49 don't worry, I'm going to show you an Excel example in a second, it's very easy.
• 00:55 It can be used to multiply arrays tables as well,
• 00:58 as long as they've got similar dimensions.
• 01:01 And it's really quite useful because it treats non-numeric
• 01:04 data as if it were zero rather than roughing out.
• 01:07 Very, very powerful, best thing to just get on with some examples.
• 01:11 When I'm trying to explain SUMPRODUCT using the slides, if you've not
• 01:16 seen SUMPRODUCT before, you're probably thinking, what does that mean?
• 01:19 Not quite with you, Liam.
• 01:20 So let me try and
• 01:21 explain its very simple concept with this easy to understand example.
• 01:27 I've summarized here all the sales I made in a particular shop on a particular day.
• 01:32 Essentially everything in the shop has only one of several pricing points.
• 01:36 Be it $1, $2, $5, $10, etc.
• 01:41 And I made 14 sales of $1, 7 of $2, 9 of $5, and so on.
• 01:47 The question is, how many sales did I make in general?
• 01:51 Well, to work this out,
• 01:53 I can do it long hand by first of all calculating the product.
• 01:58 I'll go in here, so okay, type the product, and
• 02:02 then multiply them together, equals that times that.
• 02:07 Now, a little shortcut for you,
• 02:08 you see that little black dot on the bottom right hand corner of the cursor?
• 02:12 If I double-click on this and I put a column to the left,
• 02:16 it will actually copy down that whole column for you.
• 02:20 Now if I sum those, ALT+=, I get $3,198.
• 02:25 Or, I could have gone here and gone =SUMPRODUCT, open brackets,
• 02:31 this range, the pricing points come at that range.
• 02:37 Close bracket, does exactly the same thing.
• 02:41 Yep, SUMPRODUCT takes each range and
• 02:43 multiplies the corresponding elements together and then adds them all up.
• 02:49 That's what it does.
• 02:49 Amazing, maybe not.
• 02:53 But, if I change this comma to a times, look what happens.
• 03:00 Isn't that just brilliant?
• 03:05 The silence around here is deafening at the moment,
• 03:07 you're probably going, is he finished yet?
• 03:10 What's he gonna talk about next?
• 03:14 Look, let me explain how important that result is by doing this.
• 03:17 Now change the times to divide, press Enter.
• 03:23 Can't you see just how Earth-shattering this is?
• 03:28 No Liam, I'd rather you stop shouting down the microphone at me if you wouldn't mind.
• 03:33 Okay, let me explain.
• 03:34 A set of product here, how about I put in, Division.
• 03:41 Although we don't have to get that mixed up with the business unit, and
• 03:44 make that equal that divided by that.
• 03:47 And copy this down.
• 03:52 1661.246, 1661, and if I type this, .246.
• 03:57 Do you see, it is a shorthand way of doing lots of calculations at the same time?
• 04:04 And that's useful.
• 04:06 Let's have a look at the SUMIFS example from last time.
• 04:10 You recall we had business units A, B, C, and D.
• 04:14 Two salespeople, Joe and Alex.
• 04:15 Four regions, north, south, east, and west.
• 04:18 And two tax points, yes or no.
• 04:20 Now what we want it to do, if you recall, we'll say for business unit A,
• 04:24 how many cells did Joe make for the north region where they had to pay tax?
• 04:29 And we could use the SUMIF function here.
• 04:33 What it requires first of all, is the SUM range, so we have to put this in,
• 04:36 comma the first criteria range, which is the business unit.
• 04:41 Comma the actual criteria, comma the second criteria range,
• 04:45 which is the sales person.
• 04:46 Comma, who it is, comma, the region we want to actually evaluate.
• 04:51 Comma, which region, comma, the tax.
• 04:53 Comma, the tax criteria, 500.
• 04:59 I can do this with SUMPRODUCT.
• 05:07 If I go here, =SUMPRODUCT, open brackets.
• 05:14 I want another bracket.
• 05:15 I'm going to evaluate all of this.
• 05:18 That has to equal A.
• 05:22 That will give me a range of values of well, first of all I will choose true,
• 05:27 true, true, true, for these, true, true, true, true, and we false for the rest.
• 05:33 Now if I put a comma, it ain't gonna work.
• 05:38 I'm gonna put a times in here instead.
• 05:41 Because we have to multiply these things together.
• 05:43 Cuz the next I'm going to do is take this range and say okay, that has to equal Joe.
• 05:50 So that's gonna go true, true, false, true, true, false, true, etc.
• 05:53 Now true times true is 1.
• 05:56 False times true is the same as true times false, is the same as false times false,
• 06:00 is 0.
• 06:03 It's gonna give me a whole load of 1s and 0s.
• 06:06 Do this one.
• 06:09 Same idea again.
• 06:11 Do this one.
• 06:15 And then at the end multiply in the range.
• 06:23 500 does exactly the same thing. Cuz the only time it works is when this is
• 06:27 true times that is true times that is true, times that is true,
• 06:32 times 100, and it will add them up.
• 06:35 If any of them are false, they wont get included.
• 06:37 So this a brilliant way, and sometimes I think more intuitive to follow, and
• 06:41 there's so much variant even if slightly longer.
• 06:44 And next time I'm going to extend this point further.

Lesson notes are only available for subscribers.