Did you ever need to provide
an answer to this question? So, you were told to provide a total for, for example, your sales,
but not just a full sum. Only for a certain
product, a specific month, and for a specific country. Now basically, if ever need to make a sum with exceptions, and especially if these exceptions are more than one thing, which is normally the case in real life, then the sumifs function is for you. Now, notice I'm not
saying sumif, but sumifs. Now, you might already be
familiar with the sumif version, just not with the plus S one. And it's actually popped
up since Excel 2007 and surprisingly many people missed it. But it's actually such a
time saving function, though. Now, what's the difference, you might ask? Well, sumif can only
check for one exception, whereas the ifs version can check up to, what, I believe it's 29 criteria. Not that you'll ever need that much. Now, in this lecture, I'm going
to show you the sumif function as well as the sumifs version. Now, averageifs is similar to the sumifs, except that it just checks for the average instead of the sum. Countifs is slightly different, I'm going to show you that as well. Now if you've never used
the sumif function before, I recommend that you
don't learn to use it now and just learn the ifs version. The reason for this is
that the ifs version can do more than the if version and because the syntax of the two differ, you don't always have to think which one should I use now? I personally use the ifs version. Having said that, I'm going
to start off our example by showing you the sumif version of it and just explain to you why
they differ and how they differ. Here we have our months, products, country, and sales revenue. From this data, we'd like to
find our total sales revenue for this criteria. So the first analysis we want to do is how much revenue did
we have in February? Because it's just one
criteria we're checking, I can use the sumif function. The syntax is that first comes the range. Which range? It's not the sum range because
the sum range is at the end. So the first range here is
actually the criteria range, so it's the place where
this criteria can be found. And that's basically here. So I'm going to click here
and then use my shortcut key, Ctrl, Shift, down. Next is the criteria itself, which is this one. And then there's the sum range. So I do the same thing, Ctrl, Shift, down. And let's check that. That looks right. Okay, so that was the sumif function. Very simple, you can see that
the sum range came at the end. Now we have two criteria
that we want to check. So we want to get the sum if it's February and if our product is shirt white. And this is where the sumifs
function comes into play. This function was
introduced since Excel 2007 and it made the lives of so
many people so much easier. Because they no had to
use complex array formulas to do this type of calculations, they could use a very simple ifs formula. And the way it works is that now the sum range is at the beginning. Because when Microsoft
programmed this formula, they couldn't leave the
sum range at the end because they don't know how many criteria you're going to be
putting in your formula. So it had to come to the front. And obviously, they couldn't
change the old formula because then it wouldn't be compatible with the older Excel versions. That's why this new
function was introduced. So our sum range is our revenue. And next is the criteria
range one, criteria one, criteria range two,
criteria two, and so on depending on how many
different criterias you have. In this case, we have two. So, the first range of our criteria, where we actually find
February, is this one. Then comes the actual criteria. And now we move on to criteria range two, so where can we find this? In here. And this is the actual
criteria we're checking for. Now let's check that as well. So we have Feb, shirt white is this one. These four, 2028, 2028. Now, in the next example, we've been given a further criteria to say
February, shirt white, and the sales that we had in the US. So we do the same thing. I'm just actually going to copy this. Just move these ones down. The ranges are the same. So, all I have to do is add an
additional criteria for USA. And so first is the
range where I can find it and then is actual criteria itself. Okay, 1618, 1618. One other thing I can
do is to use wild cards and comparison operators
like bigger or smaller or a combination of these. So, for example, if
instead of shirt white, I was actually looking
for the text in there, so I have to put quotation marks, and put shirt wh and
then put the star sign, quotation marks, it finds it. Okay, whereas if I take that
away, it doesn't find anything. So I can use this sign to find anything that starts with
shirt and then has wh in it. Now let me show you how to
use the comparison operators. So if we have this, but instead of USA, we want to add the sales revenue only if it is greater than 500. If it's not greater than
500, it shouldn't sum it. So now let's write that one. So we do sumifs, some range,
still our sales revenue, criteria one is February. Criteria two, our product. Okay, now we come to criteria three. So we want to only sum if their
revenue is greater than 500. So in this case, what's our criteria? It's the revenue. It's this one. So it can be our sum range, but it can also be our criteria. And the criteria itself is
that it's greater than 500. Let's check that. So, we have shirt white, this one, so this is not greater than 500. This is yes and that's yes. Less and that's March. 1126, 1126. The only thing you need to keep in mind when you add the comparison operators is that you need to put them, if you're not putting it in a cell and you actually want
to put it in a formula, you can't do this. You get an error. You need to put it in quotation marks because it's like text. You don't have to put necessarily your number in the quotation marks, you can also do this. Or you can have your number in a cell and do a cell reference to the number like this. So basically, I have
the greater one in there and then & connecting it to this cell. Okay, so just don't forget, when you're adding these operators, put them in the quotation marks. And that's the sumifs function. Now averageifs works
in the same exact way. The only difference is that it takes the average instead of the sum. So, in this case, you
would say averageifs. Okay, it's taking now the average of the revenue for Feb and shirts white. The count is slightly different because you don't need
one of these criterias, you don't need that sum range. So let me show you the countif first. You just need range and criteria. So we're just going to count if in this range, there is February. And so, how many occurrences
of February we can find here. That's our range. That's our criteria. Eight, so we have February eight times. That's right. Now, the ifs version allows us to check for more criteria and
count based on these. So, we say criteria range one is the same, so that's our month. Criteria range two, that's our product. So four. Basically, we have four
occurrences of February and shirt white in here. It's this one, this one, where is the other one? And this one. And the same for this. We have now three criteria to check. Again, you do the same thing. Three. One, two, and three. The other thing I'm going to show you is the advantage of Excel tables. So let's say in this
example, someone tells you, oh, we were missing something in February, you have to add this number. So I'm just going to put a big
number, let's say this one. Obviously, your values here in your report are not going to update because they're not
referencing this new line. You will have to manually expand these. But to avoid that, you
can use Excel tables. And Excel tables are great for such cases. So all you have to do is
click anywhere in this cell and press Ctrl T. This is data for our
table and it has headers. Okay, now it's an official table, and you know that you get this
table tools activated here. And now if I add my new data
to this, so, shirt white. My numbers are updated automatically. And let's click on one and you can see the range
was updated dynamically. That's the advantage of Excel tables. Now, I generally never like
this design that it gives me, so, to remove that, you just go to design and you select clear. You go back to your own
formatting of the table. The other neat thing about this is that if your table is really long, you see that it changes
your Excel column headers to the table headers when you scroll down. This is kind of like a
partial freezing panes. It only does it for here, and once you are out of your table, you see the usual column headers. As you can see, the ifs version allows you to easily add exceptions to your sum, average, and count calculations. Now, don't make your life more difficult by trying to use Excel
filters to get your sum. Use the ifs version instead. Now it's your turn. Go ahead and do the
exercise for this lecture. (warm exotic music)
