Keep in mind the sequence of
my top 10 is not important. What is important is that
you know of these functions and you know how to use
them when the time comes. Having said that, I've kind
of kept the best for last. I'm not sure if sumproduct was actually designed to be this great, or if it just happened
to become like this. You've got to watch this
lecture to find out. The classical use of sumproduct is to do a sum of multiplications. Now to give you an example, you have here price and quantity. To calculate your total revenue, you're going to multiply
price by quantity, and then you add up the
values in the Revenue column. Now you have a calculated column and a total for this calculated column, which is your total revenue. With sumproduct on the other hand, you just have one formula
that does it all for you. You start by typing in sumproduct, then you just highlight your Price column, add a comma to move on
to the next argument, followed by your quantity, close bracket, and you have your results. The good thing about sumproduct is that it can work on arrays of values, and it doesn't have to be
entered as an array function. So there's no need to press
this Control + Shift + Enter like we did when we learned
the frequency function. It's kind of like an very
elegant array function. What it's actually doing is
it's taking the first cell, multiplying it by the second one, doing the same thing for the next row, and adding the result. So it basically multiplies and then sums. That's why it's important that your ranges have the same size. Let's take a more detailed
look at this function. So the way I wrote it here,
I use the comma, right? that's by default. It's like this. Now the way I generally
write my sumproduct formulas is like this, and I'm going to explain to you why by going through this example. This was a simple case. If you've ever used sumproduct, you've probably used it in this form. With sumproduct, what
some people don't know is that you can also make exceptions. So I could get the sum of,
say, in this case quantity for only Product A. We can use the same formula. And now because I'm making an exception, I'm only looking for
products that are Product A, I have to highlight this. Then I say equals this. This is something you have
to be so careful with, is the brackets here. So whenever you use this
equal sign in sumproduct, make sure your argument is
separately inside brackets. And then let's say I use the normal argument with this comma. Okay, so I want to move
on to the next array. I want Quantity. So all I have to do is highlight this, and you see, make sure your ranges are obviously the same size. So I'm going to close this. Enter. Does it work? And now let me switch this to this. It works. Why? Because what this does
here is it's checking if this is Product A true or false. So that's the result of Excel's check. In this case it's true, true,
true, false, false, true. When you leave it with the comma, it leaves the true false text, and it can't do anything
with it, so it's zero. When you use the multiply sign and Excel tries to multiply
a cell that boolean, so that's true or false, it
turns it into one and zeros. So basically this these
become one, this zero one. So it does one multiplied
by 200, that's 200, plus one multiplied by 33,
plus one multiplied by 100, and so on. That's why I use the multiply sign. Let's just see if our number is correct. So let's just add up wherever we have product
A, the quantities. That's 443. So that works fine. If you do want to use the comma, what you can do is you have
to use some numeric operator on this cell. What some people do is that they do this, this is basically minus minus, to get this. So it's kind of like you're multiplying it by a minus one reversing it. You can also do like
one multiplied by this. Okay, as long as you're doing some type of multiplication on this, Excel is going to translate
the true false to one zeros. So in order to just avoid all that, I just change this comma to this. Now moving on to calculate our revenue. I do the same thing. I have sumproduct. I want my exception in there again, so don't forget the bracket. Let's just assume I don't
have this column here. I'm going to do price, and then I can do again
the times quantity. To double check, that's going
to be these and this one. That's that. Now what most people don't know is that sumproduct can do
so much more than this. Because of its nature of
the way it's designed, it can do vlookup or
index and match can do. It can also do what sumifs can do, and do it even better than sumifs can. Now stay with me in this one, because understanding this function is going to save you from doing a lot of in
between calculations and unnecessary workarounds
in your existing Excel files. Good thing about sumproduct is that it can also do or conditions. And that's something you can't do with, for example, sumifs or countifs. If you remember when we
covered this function one where we cover sumifs, we could only check for one thing, for one criteria in each column. So we could only check if company is A, but we couldn't do both. We couldn't do an or, A or B. We were restricted in each
column to one condition. We are not with sumproduct, because you can use or conditions here. So let's see how you can do that. In this case, I want to see if
it's Company A or Company C. That's my year and my month. And look, I'm doing a count. I'm going to use sumproduct to count. I'm going to show you how. Now I'm saying if company is A or B. So, again, brackets are
very important in this case. This is my company if it equals to this one,
so I'm going to close that. It's my first argument. Now the or argument is a plus here. That's the or. This is and, and this is or. Then if this range equals this one, close bracket. My whole or condition needs
to be in separate brackets. Can you see that? Now Let's just press Enter,
let's see what happens. I get a number. So that should mean that
there are seven instances of Company A and C in here. That's right. So I'm actually also doing a count, but I'm not done with my argument because I still want to
check for a year 2014, and month to be January. So right here, I'm going to open bracket, year equals this. Remember, whenever you
have the equal sign, put that part in brackets. And my month. So it should be five instances
for this combination. Now, what I'm going to
show you, let's check that, but let's check that here, because what I've done down here is to show you the
sumproduct in slow motion, what it's doing. What our formula does is it
turns these to true false as a first instance. So in our case, we saw like
Company A or C, 2014 Jan. These lines are the ones where everything is true. As a second step, because
we're multiplying, it's turning them into one and zero. So basically it goes through this, and does one times one
times zero, that's zero. Zero times one, times one, zero. These are one, and these are one. So in total, you can see
I have five instances where I get a one. And that's how it works. In the same manner, we
have the sum for quantity. So we want to sum quantity, but this is still our criteria. To make things simple, I'm just
going to copy this formula. Paste it here. Okay, because until here,
that's what we need, is identical to what we needed for count, but now I just need to
bring in my quantity column. That's 470 because,
let's double check here. It does this times this times
this, and then times this. So that basically is one
times this, one times 90, one times 100. And that gives us 470. That's our 470 here. Now I'm going to move on to revenue. So I just want to multiply
Quantity by Price. Let's just assume we
don't have this column. So all I have to do is bring in the Price. Just expand on it, and you get more. That's my Revenue. That's this one. As the last one, I have Average Price. How could I calculate this in this case? Because remember, the sum of products is always adding, it's summing. So how could I get the average price? Well, I could get the sum of price, and then divide by my count, right? Again, it's the same thing,
but let's just not be lazy. Let's just write this really quickly. Our exceptions comes first. I know that I want the or conditions, so I'm just going to do
two additional brackets. This equals this plus... Again, bracket, I keep forgetting that. But wait, I'm going to show you what happens if you don't do the bracket. So let's do this, equals this without bracket. Value. Let's just put the brackets. So now I just have to add this one. You see here I don't need a bracket. I don't have an equal sign,
so I can leave that as is. That's going to be my sum of price. So then I can just say
divided by this one. I've already done the calculation here. That's going to be my Average Price. Let's just double check. Average Price. Obviously, these are dynamic. So if I change this to Company B or C, the results change as well. You see why sumproduct is such an unusual and useful function. It's not just the sum of products, but you can use it as a countif function, a sumif function, and
also a lookup function. Now pay attention to your brackets though, because their placement
is really important. If you're writing this for the first time, double check your results, and then trust it. If you ever forget about the syntax, like what was the plus for, can I use it, was it the asterisk, or the comma, or the brackets, come back to the demo workbook
to refresh your memory. There is no shame in that. I do it myself. Now it's your turn for an exercise. (upbeat music)
