Sumif Formula in Excel
Hello Everybody, Welcome to Excel Made Easy
This your excel guru and today with a formula called Sumif this is a pretty comprehensive formula and I would be covering it with 10 variations I have taken example of sales reps name units sold unit value and there amount and then we will be finding out these totals based on conditions like the names, units sold and so on, I have already done this so I will be doing along with you, let’s see, let’s go with the example
first the example overhere is of sales rep names of Jack and Jill then comes X Men then Superman and Spiderman the next one again and Jack and the next but obvious Superman once again these are the units these people have sold these are the unit values and these are the amounts, we can do the sums based on conditions like name this formula would starts like this SUMIF, i choose a range this is my range we have to understand that range and some range are different as we grow long and explain it easily range then you need to choose a criteria since we are doing it by name you can choose a name says is Superman click over there just type in Superman and then sum range , sum range is the actual range which you need to sum, This range is the range which contains the criteria that sum range is the range with needs to be summed now i close the Bracket and here’s the answer 148 , I delete these answers so 148 is the answer Superman Superman has sold in 148 units so if I can do it by SUMIF by units sold
also SUMIF by range again would be unit sold this time the criteria would again be in the unit sold would be say 50 and now the sum range becomes redundant this field because you are doing the total out of unit your range itself , so you can close the formula here itself I am so sorry we did a mistake over here
= SUMIF now my range is this and my criteria is 50 and my sum range would be amount I would like to find out what would be the sum if the condition of 50 and I close the Bracket and the answer is 50 now comes greater than unit sold =SUMIF a range would be this, lets choose a range and the criteria would be unit sold greater than 350 will take 50 as the standard close comma and what do I want to total the amount column so this would give your answer the greater than 50 = if I want to say less than , this is we gonna do less than , its its pretty cool formula , less than 50 and comma and my sum range is the amount column this is it now less than = how we are gonna do it we are gonna choose sum range comma our criteria would be less than = 50, yes comma and this is our range and we choose the range this is the answer we do a greater than SUMIF,this is fun doing excel like this I love it and criteria would be you have to see what is being done greater than 50 then some range would be the amount column close the bracket oh this one is awesome you wanna total everything other than something specific other than Something specific, so you are omitting something so I m omitting 50 since that’s the standard value we are calculating upon so this is the range comma greater than less than 50 inverted comma close comma you do a total of the amounts now if I don’t want to do a total of the amount itself I want to do total over here itself I want the total of the unit sold except 50 any any any unit other than 50 so this is the answer 453 it has totalled up the all the units sold except 50 where it could have been 50 say by typing 50 over here It is going down this is good and this can be done with name also is equal to SUMIF you choose your range comma your criteria is now say are you want you omit Superman and you want the total based the amount you choose this column and press Enter so this is 3200 everything other than what Superman has done .
It can also be with names starting with that’s cool that’s so superstitious people that we have some special in India you have so many people were so superstitious they will not hire people with starting with names like S or something so that’s true that’s true I believe that grow in many other Western countries also so I take a sum range this is the sum range and comma the criteria is that the name should be starting with S so you type S and here comes the tricky part you type multiplication sign or star or asterisk as you said inverted comma close and your sum range you want to find out the unit sold by people who have a name starting with S so they have one 198 units sold so people with starting name with S are good And you can also have the same with the digits in a character in a name for example SUMIF range is this and I want a total of peoples units sold if the name starts with an S have ever has 8 character following it 12345678 only spiderman comes in this category so Let Us see the answer should be 50 that’s correct answer is 50 so this is the SUMIF formula with 10 variations pretty useful variations you can try them along a better you go to my website excelmadeeasy.weebly.com and download free no charges and all this is my website you going to the training files page download it for free .
If you believe that what I am doing is right if you believe that what I am doing is good and should be shared with the world then I would like you to go ahead and click over here and play and follow me on Twitter this is my Twitter page not only follow also retweet the work also you can go to my Facebook page from my website itself and once you click over here it will take you to my excel made easy Facebook page and you can like me over here not only like but you can also share my word which is more important you are watching this video on YouTube you can subscribe by clicking over here so that whenever there is a new video uploaded by me which is almost every day you get to know about it and then also you can share my work my motto is let’s share knowledge so this is your excel guru signing off for the day. Have a wonderful day ahead. Happy Excel Learning!