SumIf Formula In Excel

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.

SumIf Formula In Excel

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 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!

Our Other Services

Read Free Ebooks

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!

Buy Excel Course

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788

Create Best Looking Resume

At you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!

Download Excel Crash Course

This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.

Learn Photoshop Web Designing

You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .

Download Full list of Excel Shortcuts

This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.

Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on

Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.

List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.

Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You