馃攳
Excel SUMIFS (better version of SUMIF), COUNTIFS & AVERAGEIFS (Multiple Criteria) - YouTube
Channel: Leila Gharani
[3]
Did you ever need to provide
an answer to this question?
[6]
So, you were told to provide a total for,
[8]
for example, your sales,
but not just a full sum.
[11]
Only for a certain
product, a specific month,
[15]
and for a specific country.
[17]
Now basically, if ever need to make a sum
[20]
with exceptions, and especially if
[23]
these exceptions are more than one thing,
[25]
which is normally the case in real life,
[27]
then the sumifs function is for you.
[30]
Now, notice I'm not
saying sumif, but sumifs.
[34]
Now, you might already be
familiar with the sumif version,
[37]
just not with the plus S one.
[40]
And it's actually popped
up since Excel 2007
[43]
and surprisingly many people missed it.
[46]
But it's actually such a
time saving function, though.
[49]
Now, what's the difference, you might ask?
[52]
Well, sumif can only
check for one exception,
[55]
whereas the ifs version can check up to,
[58]
what, I believe it's 29 criteria.
[61]
Not that you'll ever need that much.
[63]
Now, in this lecture, I'm going
to show you the sumif function
[66]
as well as the sumifs version.
[69]
Now, averageifs is similar to the sumifs,
[72]
except that it just checks
[73]
for the average instead of the sum.
[76]
Countifs is slightly different,
[77]
I'm going to show you that as well.
[80]
Now if you've never used
the sumif function before,
[84]
I recommend that you
don't learn to use it now
[87]
and just learn the ifs version.
[90]
The reason for this is
that the ifs version
[93]
can do more than the if version
[97]
and because the syntax of the two differ,
[100]
you don't always have to think
[102]
which one should I use now?
[104]
I personally use the ifs version.
[107]
Having said that, I'm going
to start off our example
[110]
by showing you the sumif version of it
[112]
and just explain to you why
they differ and how they differ.
[118]
Here we have our months, products,
[120]
country, and sales revenue.
[123]
From this data, we'd like to
find our total sales revenue
[128]
for this criteria.
[131]
So the first analysis we want to do
[133]
is how much revenue did
we have in February?
[138]
Because it's just one
criteria we're checking,
[141]
I can use the sumif function.
[144]
The syntax is that first comes the range.
[147]
Which range?
[148]
It's not the sum range because
the sum range is at the end.
[153]
So the first range here is
actually the criteria range,
[157]
so it's the place where
this criteria can be found.
[161]
And that's basically here.
[165]
So I'm going to click here
and then use my shortcut key,
[168]
Ctrl, Shift, down.
[172]
Next is the criteria itself,
[176]
which is this one.
[179]
And then there's the sum range.
[180]
So I do the same thing, Ctrl, Shift, down.
[184]
And let's check that.
[188]
That looks right.
[191]
Okay, so that was the sumif function.
[193]
Very simple, you can see that
the sum range came at the end.
[198]
Now we have two criteria
that we want to check.
[201]
So we want to get the sum if it's February
[203]
and if our product is shirt white.
[206]
And this is where the sumifs
function comes into play.
[211]
This function was
introduced since Excel 2007
[215]
and it made the lives of so
many people so much easier.
[220]
Because they no had to
use complex array formulas
[223]
to do this type of calculations,
[225]
they could use a very simple ifs formula.
[231]
And the way it works is that
[235]
now the sum range is at the beginning.
[238]
Because when Microsoft
programmed this formula,
[241]
they couldn't leave the
sum range at the end
[244]
because they don't know how many criteria
[246]
you're going to be
putting in your formula.
[248]
So it had to come to the front.
[250]
And obviously, they couldn't
change the old formula
[253]
because then it wouldn't be compatible
[255]
with the older Excel versions.
[258]
That's why this new
function was introduced.
[261]
So our sum range is our revenue.
[265]
And next is the criteria
range one, criteria one,
[270]
criteria range two,
criteria two, and so on
[272]
depending on how many
different criterias you have.
[275]
In this case, we have two.
[278]
So, the first range of our criteria,
[282]
where we actually find
February, is this one.
[286]
Then comes the actual criteria.
[289]
And now we move on to criteria range two,
[292]
so where can we find this?
[294]
In here.
[297]
And this is the actual
criteria we're checking for.
[303]
Now let's check that as well.
[305]
So we have Feb, shirt white is this one.
[313]
These four, 2028, 2028.
[319]
Now, in the next example, we've been given
[321]
a further criteria to say
February, shirt white,
[325]
and the sales that we had in the US.
[328]
So we do the same thing.
[330]
I'm just actually going to copy this.
[338]
Just move these ones down.
[340]
The ranges are the same.
[342]
So, all I have to do is add an
additional criteria for USA.
[349]
And so first is the
range where I can find it
[353]
and then is actual criteria itself.
[362]
Okay, 1618, 1618.
[366]
One other thing I can
do is to use wild cards
[370]
and comparison operators
like bigger or smaller
[373]
or a combination of these.
[375]
So, for example, if
instead of shirt white,
[381]
I was actually looking
for the text in there,
[382]
so I have to put quotation marks,
[385]
and put shirt wh and
then put the star sign,
[391]
quotation marks, it finds it.
[396]
Okay, whereas if I take that
away, it doesn't find anything.
[399]
So I can use this sign to find
[402]
anything that starts with
shirt and then has wh in it.
[407]
Now let me show you how to
use the comparison operators.
[410]
So if we have this, but instead of USA,
[413]
we want to add the sales revenue
[416]
only if it is greater than 500.
[421]
If it's not greater than
500, it shouldn't sum it.
[426]
So now let's write that one.
[428]
So we do sumifs, some range,
still our sales revenue,
[435]
criteria one is February.
[441]
Criteria two, our product.
[447]
Okay, now we come to criteria three.
[449]
So we want to only sum if their
revenue is greater than 500.
[455]
So in this case, what's our criteria?
[459]
It's the revenue.
[461]
It's this one.
[462]
So it can be our sum range,
[463]
but it can also be our criteria.
[469]
And the criteria itself is
that it's greater than 500.
[476]
Let's check that.
[478]
So, we have shirt white, this one,
[480]
so this is not greater than 500.
[483]
This is yes and that's yes.
[488]
Less and that's March.
[491]
1126, 1126.
[494]
The only thing you need to keep in mind
[496]
when you add the comparison operators
[500]
is that you need to put them,
[503]
if you're not putting it in a cell
[506]
and you actually want
to put it in a formula,
[509]
you can't do this.
[513]
You get an error.
[514]
You need to put it in quotation marks
[517]
because it's like text.
[522]
You don't have to put necessarily
[523]
your number in the quotation marks,
[525]
you can also do this.
[528]
Or you can have your number in a cell
[532]
and do a cell reference to the number
[536]
like this.
[538]
So basically, I have
the greater one in there
[541]
and then & connecting it to this cell.
[547]
Okay, so just don't forget,
[549]
when you're adding these operators,
[552]
put them in the quotation marks.
[556]
And that's the sumifs function.
[558]
Now averageifs works
in the same exact way.
[564]
The only difference is that
[565]
it takes the average instead of the sum.
[568]
So, in this case, you
would say averageifs.
[574]
Okay, it's taking now the average
[577]
of the revenue for Feb and shirts white.
[584]
The count is slightly different
[587]
because you don't need
one of these criterias,
[590]
you don't need that sum range.
[592]
So let me show you the countif first.
[597]
You just need range and criteria.
[599]
So we're just going to count
[601]
if in this range, there is February.
[603]
And so, how many occurrences
of February we can find here.
[609]
That's our range.
[611]
That's our criteria.
[613]
Eight, so we have February eight times.
[617]
That's right.
[620]
Now, the ifs version allows us to check
[623]
for more criteria and
count based on these.
[627]
So, we say criteria range one is the same,
[631]
so that's our month.
[635]
Criteria range two, that's our product.
[643]
So four.
[644]
Basically, we have four
occurrences of February
[647]
and shirt white in here.
[649]
It's this one, this one,
[653]
where is the other one?
[655]
And this one.
[660]
And the same for this.
[662]
We have now three criteria to check.
[668]
Again, you do the same thing.
[681]
Three.
[684]
One, two, and three.
[691]
The other thing I'm going to show you
[692]
is the advantage of Excel tables.
[695]
So let's say in this
example, someone tells you,
[697]
oh, we were missing something in February,
[700]
you have to add this number.
[702]
So I'm just going to put a big
number, let's say this one.
[711]
Obviously, your values here in your report
[715]
are not going to update
[716]
because they're not
referencing this new line.
[719]
You will have to manually expand these.
[722]
But to avoid that, you
can use Excel tables.
[727]
And Excel tables are great for such cases.
[730]
So all you have to do is
click anywhere in this cell
[733]
and press Ctrl T.
[736]
This is data for our
table and it has headers.
[742]
Okay, now it's an official table,
[744]
and you know that you get this
table tools activated here.
[748]
And now if I add my new data
to this, so, shirt white.
[760]
My numbers are updated automatically.
[762]
And let's click on one
[764]
and you can see the range
was updated dynamically.
[768]
That's the advantage of Excel tables.
[772]
Now, I generally never like
this design that it gives me,
[777]
so, to remove that, you just go to design
[782]
and you select clear.
[784]
You go back to your own
formatting of the table.
[787]
The other neat thing about this
[789]
is that if your table is really long,
[792]
you see that it changes
your Excel column headers
[797]
to the table headers when you scroll down.
[800]
This is kind of like a
partial freezing panes.
[804]
It only does it for here,
[806]
and once you are out of your table,
[808]
you see the usual column headers.
[813]
As you can see, the ifs version allows you
[816]
to easily add exceptions to your sum,
[819]
average, and count calculations.
[821]
Now, don't make your life more difficult
[823]
by trying to use Excel
filters to get your sum.
[827]
Use the ifs version instead.
[829]
Now it's your turn.
[830]
Go ahead and do the
exercise for this lecture.
[835]
(warm exotic music)
Most Recent Videos:
You can go back to the homepage right here: Homepage