Hello and welcome to our new video. In this video we are
going to learn about two formulas SUMIF and SUMIFS. We have
already covered about SUMIF in last video.
In this video primarily focus will be on SUMIFS. In this
video we are going to learn about the difference between
SUMIFS and SUMIFS and in which criteria it is different.
First of all I will open excel and here I have some data on
excel sheet. Here month is written from January to January
2022.
Here you can see some names of sellers and they have sold
some items. Here it is written how many pieces a particular
person has sold.
For example in January 2020 Rahul has sold keyboard and how
many pieces he has sold? He has sold 16 pieces.
In February 2020 Deepak has sold keyboard and how many
pieces he has sold? He has sold 20 pieces.
In March 2020 Aakash has sold mouse and how many pieces he
has sold? He has sold 18 pieces.
In January 2022 Deepak has sold keyboard and how many
pieces he has sold? He has sold 16 pieces.
So this is a shop named The Memory Deal Electronics and all
the data of this shop is of this shop.
And here we are given that I have made some table type
where we will know that how many keyboard, mouse and pen
drive a particular person has sold.
So this thing we have seen in last video that if we use
SUMIFS formula then what we can get?
For example, here keyboard is written multiple times and
here mouse is written multiple times.
So if I want to know from these items that how many
keyboard has been sold or how many keyboard pieces have
been sold then you can tell me in total.
I have already done this with SUMIFS formula.
But what does SUMIFS formula do?
SUMIFS formula tells us that how many keyboard pieces have
been sold.
So I want to know that Rahul has sold keyboard from January
2020 to January 2022 and how many pieces he has sold.
So I will put this criteria and use SUMIFS formula.
So first of all I will see SUMIFS formula and then I will
see SUMIFS.
Now it can be a confusion that SUMIFS and SUMIFS are same
in sound.
So first of all we see SUMIFS formula.
So in SUMIFS formula we know that how it is used.
First of all, sign of equals to is given.
Then it is written SUMIFS.
And here it has suggested you.
So now what will you do?
You will double click.
Here it is asking range.
Now which range do you want to take?
From which we have to find out.
We have to find out that how many keyboard pieces have been
sold or how many mouse pieces have been sold.
So what you have to write in range?
You have to give the whole range.
So we have given this range.
After that you have to put comma.
And here it is asking criteria.
Now what do we put in criteria?
In criteria we put the particular thing about which we need
information.
I have to find out that how many mouse pieces have been
sold.
So tell me the total number of pieces.
So here it is mouse.
So either you select directly from here.
Or what will you do?
We write here in double quote.
Mouse.
Ok.
Here we write mouse.
And close the double quote.
Then here we have to put comma.
And at last it is asking tell me the sum range.
Ok.
So you have to give the sum range.
Only then it will be able to tell the total number of
pieces have been sold.
So what you have to write in range?
In sum range.
Whatever is the range of your pieces.
You have to select this.
You will select this.
From D4 to D28 it was selected.
Now what you have to do?
Now you have to close the bracket.
First of all what we have taken?
First of all we have taken items.
Ok.
So for items we have put C4 to C28.
We have selected all the items.
After that I have to find out that from these items one
particular item which is mouse.
I have to find out that how many pieces have been sold.
So to find out that how many pieces have been sold.
What I have done?
I have put sum range.
After putting these three things.
What you have to do?
Then we will get to know that 146 pieces have been sold.
From January 2020 to
January 2022.
I will put mouse here.
Ok.
So here we got to know that how many pieces of mouse have
been sold.
Apart from this, there is no table in this table.
This is just data.
So if we want to find out how many pieces of keyboard have
been sold.
We can find out that total number of pieces of mouse is 146.
Ok.
But from these 146 pieces of mouse,
I have to find out that how many pieces of Rahul have been
sold.
Or how many pieces of Deepak have been sold.
Or how many pieces of Aakash have been sold.
So how will we find that?
Sum if formula can tell us by putting one criteria.
But in sum if formula, we can put multiple criteria.
How can we put multiple criteria?
First criteria is
Total number of keyboard pieces have been sold.
Ok.
Second criteria is
Name of Rahul.
So we can put multiple criteria in sum if formula.
So here I have to use sum if formula.
So first of all, put equals sign.
Now it has shown that there is a formula like sum if in
excel.
Ok.
So you have to double click it.
After that, it asks what is the first parameter?
First parameter is sum range.
In sum if formula, it was asked at last.
Here it says tell me first what is the range of sum.
So we select it.
After that, it asks tell me range 1 of criteria.
Ok.
In range 1 of criteria, we have to put all the people
working in our shop.
We have to put them.
So, we have selected this range.
After selecting here, the third parameter is criteria 1.
Now it is asking to select range of criteria.
Then it is asking to select criteria.
Now you have to put comma here and select criteria.
As we did in sum if formula, first we selected range.
After selecting range, it asked to select criteria.
Criteria means tell me total keyword.
When we selected range here, we have to tell data of that
particular person.
We want to know
Rahul's keyword.
So, we have to put particular name here.
So, either put Rahul in double quote or select Rahul.
So, we selected this cell.
After that, you have to put comma.
After putting comma, it will ask tell me range 2 of
criteria.
Ok.
What we have to put in range 2?
We have to put items in range 2.
Ok.
We have to find out
Rahul's keyword.
So, it should be items.
So, we have to select these items.
We selected.
After that, we have to put comma.
After putting comma, it will ask same thing.
As we did in sum, first we selected range of criteria.
First, we selected all these names.
Then we told we want Rahul's data.
So, we put Rahul in double quote or selected from here.
Similarly, when we selected range of other item, we have to
tell which item you are searching.
Which item's data do you want?
I want keyboard. So, either put keyboard in double quote.
If you don't want to put in double quote, select it from
here.
After selecting from here,
I entered.
Rahul sold 66 items of keyboard.
Now, what was the total?
Now, we took out mouse.
I will show you
Rahul's mouse.
Before that,
I have to drag and find out how many keyboards
Deepak or Akash sold.
If we want to find this, what we have to do?
We have to remember that these ranges have to be locked.
If we don't lock these ranges, they will run and tell us
how to lock these ranges.
So, we are going to do the same.
Leaving this name, what we have to do?
We have to lock all other ranges.
So, what I have to do from here?
See, our name is in E4.
So, what we have to do?
Leaving this, we have to select all other ranges.
And what we have to do?
Press F4. keyboard and press F4 now you will see that in
range dollar is included dollar D, dollar 4 dollar D,
dollar 4 dollar D, dollar 28 now we have locked the range
so it will not run here and there after E4
I will lock all my ranges so I will press F4 now dollar is
included so I will not press E4 so we have to remove E4
because if we select E4 it will be named so I have to do
other things so I will press F4 now you can see
E4 does not have dollar all other ranges have dollar so we
have locked it after doing this now you have to enter 66
now you can drag so we know that how many pc did Deepak
sell from January 2020 to January 2022 this is 130 from
whom did Aakash sell 0 let's check
Aakash sold mouse
Aakash sold pen drive
Aakash sold mouse
Aakash sold mouse so he sold 0 now we have to find mouse
how many mouse did Rahul sell same thing we have to do same
formula first we have to do equals to then we have to do
double click after double click it asks us what to put in
sum range so we selected it after selecting what we have to
do we have to tell criteria range 1 we have to select
criteria range 1 after selecting we have to find data of
Rahul we have to find
Rahul or we have to select after selecting we have to
select criteria range 2 from here we have to select all
items after selecting we have to find data of Rahul we have
to find data of Rahul after selecting we have to find data
of Rahul we have to tell data of mouse
Rahul Rahul Rahul Rahul Rahul Rahul Rahul Rahul Rahul Rahul
Rahul Rahul so you can see that what you have to do only
you have to lock range what i will do other things i will
lock the range so from here lock the range and from
keyboard press F4 press F4 here similar select range after
E4 select range after E4 and from here press F4 and after
doing this press enter now drag it now you will get to know
Deepak also did 0
Aakash did 146 both of them did 0
Rahul and Deepak did keyboard so we got to know now we have
to know how much pen drive Rahul sold so it will be same
for pen drive at least mouse will know pen drive is 0 how
to know apply formula so again write SUMIFS and here put
range of sum so we know now select all of them and after
selecting now put criteria range 1 what is criteria range 1
this is name select all of them select all of them now put
comma and put criteria
Rahul has to know how much pen drive Rahul sold so here in
double quote you can write name or directly select from
here after putting comma tell me criteria range 2 criteria
range 2 is items range select all of them after doing this
tell me criteria range 2 after criteria range 2 what is
criteria 2 criteria 2 is pen drive now pen drive write in
double quote or select from here after doing this enter
after entering mouse sold 0 but pen drive 99 now drag it
let's try drag it here one mistake
is not locked so lock range first so here what we did we
select from here and what we pressed
F4 just give me a second select from here so what we did
lock range before Rahul we don't want to select name if we
select name so after that from C4 lock range after Rahul so
from here press F4 now enter and my range is 99 now drag it
99 99 99 C4 change it delete it and instead of Rahul select
E4 once and now enter 99 now drag it now it is correct
because of that it was wrong try to put cell name 99 40 26
Rahul from January 2020 till January 2022 sold keyboard 66
sold mouse 0 sold pen drive 99
keyboard 130 sold mouse 0 sold pen drive 40 sold keyboard
130 sold mouse 0 total pieces keyboard plus mouse plus pen
drive formula of sum and range from here to 99 enter total
items total total pieces
Rahul sold 165 now from sum if we know that Rahul sold how
many pieces of particular item but we have to do so many
things that we have to put sum if formula and then we have
to remove all these things so here if you remember we did
one thing in last videos we did pivot table in pivot table
also we were doing same thing so here I will use pivot
table and then you will come to know if you have pivot
table you don't have to do all this you can do using sum if
but pivot table is better option so
we have to pivot table first what you have to do here I
have selected whole table go in insert here I have selected
table and range here range is selected that we have selected
After this, it is asking whether you want it in the
existing worksheet or in the new worksheet, we said, give
it in the existing, brother, give it in this, okay, you
will have to put the location here, where will the pivot
table be made, so I said, show us here in this cell, okay,
what you have to do from here, you have to do it, so look
at this, this pivot table has been generated, and here
these items are above us, there are headers, month, name,
item, pieces, this has come, so let's select
from here, let's select the name, let's select the item,
and let's select the pieces, okay, and if we do it from
here, we do not do it, we do three things from here, for
now, name, item and pieces, okay, so here we will know that
see, Rahul has sold how many pieces in total, 165, Rahul
has sold how many here, 165, how many keyboards have been
sold, 66, how many pen drives have been sold, 99, how many
keyboards have been sold, 66, how many pen drives have been
sold, 99, now come to Deepak, how
keyboards did Deepak sell, 130, total 170, okay, 130 plus
40, how many keyboards did he sell, 130, 140, 240?
How much pen drive you sold? 40 total 170 How much mouse
you sold? 146 How much pen drive you sold? 26 total 507 How
simple it is we select this table go to insert select pivot
table and select this header so this much we don't have to
use
SUMIFS formula but pivot table makes our work easy right so
we understood that we can use
SUMIFS formula but pivot table is a better option always
pivot table is better option if you know how to use pivot
table you can watch last video and don't watch last video
so in this video we told you how to use it so i think you
understood how to use it remove pivot table select and
delete it will be removed i hope you understood how to use
SUMIFS formula you understood if you have any doubt you can
watch last video and practice i will meet you in next video