Hello and welcome to our new video
In this video we are going to talk about
SUMIF FORMULA
SUM and SUMIF are two formulas which sound similar but what
is the minor difference we are going to understand in this
video.
So first of all
I open excel
Here you can see some data
You can see some names
You can see their marks
Along with that you can see one more thing
Here you can see repetition
Rahul is also here
So in one list you can see repetition Along with that you
can see Deepak
Deepak is also here
So first of all before using
SUMIF FORMULA we will make it simple and see how we can sum
it
So we know how to sum
Equals to and let me increase it a little
Here we put
Equals to sign and after that we write SUM
This is what we used to do
Give me a second
Now it is fine
Here we used to write Equals to and after that we used to
give range
I have given range from here to here and after that I have
entered
So what is here? 713 But where am I going to use SUMIF
FORMULA
I will use SUMIF FORMULA where my list will have repetition
Suppose I want to know
Rahul is coming twice in this list or Deepak is coming
twice in this list
Then calculate Rahul's marks and tell me
Or calculate Deepak's marks and tell me
So we will do that thing with SUMIF FORMULA
So I go here
Select one cell and here I
I am going to use SUMIF FORMULA
So For SUMIF FORMULA
I have to write equals to and after that I have to write
SUMIF So I double clicked here
Now it is asking me to give range
So which range we are going to give
You can see this name
You have to find this name
Rahul's name
Sum it
So what I will put in range
I will put range from here to here
After that
Second thing is criteria
In criteria I have to tell
In which name I want to add marks
In which particular name I want to add marks
So here I will put Rahul in double quote
After doing this
It is asking you range of sum
For range of sum what I have to do
Range of sum is numbers from here to here
I gave this range
I closed bracket and entered
So I got 115
Is it correct
So let's see Rahul's marks are 80 and here 35
So how much it is 115 So if we drop it down So It is not
giving correct data
It is giving 35 35 35 35 0 0
Because what we have put in criteria
We have put
Rahul's name in criteria
So if you
Apply on everyone
Apply on Imran
We are not doing this
In list
Show me Rahul's name
Sum it
So it is clear
But it will be difficult
Suppose Deepak is also coming twice So I will put Deepak
And After putting Deepak
It will show me Deepak's sum
But what is the benefit
Like Deepak's sum is 67
Plus 98
But I have to go again and again
In this formula and change name
So before this we have seen
How we used to do
If such things used to come
We used to select any cell from outside
We used to put name in that cell
We have done this before
So now
Let's do it again
Let's remove this name
And select any cell
Where we will put name
And it will show value on its base
Like I have taken this cell
I3 And now I have entered
You can suppose what is coming 0 Because we have not put
any value here
So now
Let's put Deepak
So we have put Deepak
And it shows 165
It was same
But now you will say
This is also
Difficult I have to put again and again
If I put Imran
What should be the result
Result should be 91
Because Imran is once
If it is twice
It will give data
If it is once then it will show single
But apart from this we have seen
We have seen
There used to be a drop down list
In which all names used to come
If you select any name
Value used to change in this cell
Now where do we do that
I have done this in previous videos
But still I repeat once
It will be revision
So we have to go to data tab
In data tab we have to go to data validation
Click here
In data validation
And here we have a pop up window
Now here
AllowAnyValue is written
What you have to select from here
You have to select list
I have selected list
After that it will ask you
Source From here to here
All the names
I have to tell
I have entered
Now you will see
I have a drop down list
If I select any name from here
Its sum
Or single value
If the name is twice
If the value is single
If the name is single
It will give the same value
If I want to see shivam
It will change to 56
If I want to see mahul
If I want to see deepak
Deepak is 165
From here
Let's do it twice
Let's see if it will change
I have changed
From here
Let's go to drop down
And select Imran
Imran Where are you
So 169 78 plus 91 169 This method is easy
We were doing earlier
We were writing a particular name
After that we selected a particular cell
Drop down list is better than that
I hope you
This video will be clear
Whatever we have done in the sum if formula
If you still have any doubt
Play this video again
I will meet you in the next video
Till then keep practicing