hello and welcome to my a listen I am your guru and in this video we will learn the 10 most used formulas in Excel like so here is a raw data sheet that I have created which we would be using to do totals or do examples alright so the first formula we are going to learn is called the sum formula it allows you to add stuff all right so how do we add stuff now so this is a bit to Lyndee okay this would be great alright and over here I’m quick light is equal to son it is the formula to bad numbers all right and now it says number one comma number two and so on so what you can do is you can select a cell containing a number press comma select another cell containing a number press comma select another cell containing a number and so on and so forth and close the bracket you can have around up to 250 two such cells in this one formula and here is your answer it is another way of doing it it is equal to some put on the bracket and select the cells for which you want to do the total like this and close the bracket it is called selecting a range you get your answer there is another way Wow we have too many options is equal to some and I fill the number five plus seven plus 13 I don’t know what it is now so this is how we do totals and there is a cool trick I will tell you just press alt and is equal to put it together and it will automatically do the total of a continuous cell range of numbers and you’ll get your answer be easy so we are done we got some formula and average now we understand that averages are not good we one should never be an average person but you need to find out the average of sales that happened from January February March April May and June so how do we do that there is a very simple form for that this is equal to let’s bring it over here so is equal to AV e or a GE and open the bracket and it says select your numbers we need it with some formula we can select our numbers one by one again you can have this number also so you can leave these two behind and the average is 581 all right that’s one way other ways is equal to average and select this range close the bracket you get the average the average sales for January to June was 463 you can also select the average using this option number seven 8404 for 206 is 8 and I’m tired of typing phone numbers and the average of first tree it’s 631 hey you know how to do averages very simple formula beautiful formula all right now what it does is it pounds in number of cells in arrange that and in numbers all right so I type in count and I select this complete area this is how we apply it close the bracket now it says 8 cells in this range contain numbers 1 2 3 4 5 6 7 and this is 8 D it is also number these three are text so it is not showing them in this count if I make this as a a T it turned into 7 by pressing control said to go back now you know how to use the count formula and counter beautiful formula again it is equal to cou and ta and it says count the number of cells in a range that are not empty now in this range we want to find out the cells which are not empty 3 there are 11 cells in this range which are not empty for example now if I delete one over here now we know there are other cells in this range that are not empty that contain anything so that’s the counter formula for you let’s move on to the next one these are all very very useful formulas concatenate all right this is beautiful amazing formula is equal to 1 in 8 and it says joins several text strings into one big string for example I happen to have the first name and last name let’s assume that this is the first name and this is the last name or let’s say this is the country name and the city name I want to have them together so I will select the country name all right enter a comma and now select the city name close the bracket and I will have India Delhi mentioned over here but it is not nice there is no space in between them all right Oh in certain cases you might want off or combine between them so I’ll change the approach I have been is equal to on Cathy need open the bracket select text one press comma into the code symbol and press space close the code symbol to a coma and now it’s select the second term which you want to have join close the bracket and you have Indian Telly you can edit this formula by double clicking over here and between this course we can mention a comma symbol now see what will happen it’s India again knee right he can do this with first names and last names very common example now if formula is a beautiful formula now by the way I’m showing you a shortcut version sure if you were to see even a more detailed version of these videos you can go to my website my ‘listen dot o-r-g and check out all these videos they also practice files available there for you to download which you can use for practicing okay next topic is if formula one of the most used formula okay hey okay and let’s see let’s see how do we give an example if sama is a logical formula which checks whether a condition is met and returns one value if true and another value if false all right so what we’ll do is we’ll if and it says logical yes my logical test can be say if this cell is equal to X say yeah all right ah value if you say can be which is the language of this country and value if false they if it is we can mention the text as okay since you want to text as an answer you have to mention it in quotes and it is not India returned aware then we can mention at the language he I’ll show you an example with numbers also and close the bracket now see this is India so we got an answer Hindi if I type in USA over here it becomes a language of course USA’s English I am going to change this and now I’m going to show an example a total sales just one formula the total sales is less than or equal to three thousand comma values pro give job comma if value is false that means if it is lesser than this so we are doing it the other way if it is less than 3000 say work harder alright and if it is false that means if it is greater than 3000 then we’d say good job I learned that from Hancock T it says work harder because you’re CEO unless then be thousand if I make it thirty one hundred good yeah hey but if you believe then I’m doing a good job dude give a thumbs up on this video alright and now we are going to use the count if formula beautiful formula very effective very useful now I want to find out is equal to come if count the number of cells within a race that meet our given criteria now I want to count the cells which contain the word is USA or say BMW all right over here so it’s asking for range so this range will be that range where your answer is located now this is a big range see I have selected this Oman oh it says criteria I can type in my criteria now all right I’m heading it over here criteria say the EMW close the bracket you have the answer BMW is five times over here alright if I say I am there’s another way of doing this if I mention Volvo over here and is equal to count if I want to find out number of time have always mentioned so I select this range okay I’m using a keyboard shortcut where I pressed shift + ctrl + the down arrow and it will click on selected in that column column and it says criteria now I’m scrolling up and instead of typing in the criteria I’m selecting this criteria I am closing the bracket now and so it’s again five always goes to fight so let me make a Mazda a Volvo it should be six now T this is six so that’s the counter formula I’m going to show you another trick to delete stuff completely even removing the formatting so that’s an additional for you additional feature you in the Home tab select clear all everything goes like that ah vlookup everybody’s favorite right so let’s learn vlookup now so far we look up J do you want to have this our names over here let’s take this example alright we and we want to find out the total sales all right the total sales let me have only one name I’m making me header for you and total sales and I give it a space all right and I give it some bigger size there we are okay for Honda now we want to find out the total sales for Honda using vlookup is equal to we look up and we it says select your lookup value so my lookup value would be Honda come ah it will read people array will be at range which contains your answer be sure that it starts from that column which contains your lookup value which is Honda excuse me so now I selected this area comma it says column index number now this is the most important part a column index number is simply way of Excel asking you from which column should I give you the result since the result is located in this column which is called total sales and it’s number should be what one two three four five but that’s not the case because our range is starting from product so the column number would start from here one two three four type in four comma zero for a exact match press ENTER and you get the answer 3,100 and so if I take all over here I’ll get the answer it’s different answer if I type MW over here and get a different answer T this is a very very simple way of using we look a formula so now you know we look up this itself deserves a thumbs up guys do give a thumbs up to this video and now a very important aspect though this is not a formula but a very important feature that is called creating a drop-down list all right now many a times while you are doing data entry you might be required to create a drop-down list say over here itself I want to create a drop-down list so how do I do that so I go over here click in this cell go to data all right and go to data validation and from here I say list and it’s it tells me a source so I will select a source I click over here and say this is my source all right and I press okay now see my drop-down list is ready I can use my vlookup with a drop down formula see the answer is changing Wow she wasn’t drop down with vlookup also this also this also comes up around a good committee and some if formula this is very very important now a some if formula says that based on a condition you can get a result say for example is equal to some F it says range criteria some range so say for example I want to find out the total sales or just BMW from this range all right so what will I do I’ll select this area its range this range is the one which contains that criteria and then say I can have my criteria mentioned over here this can be my criteria but I can even type it in OMA some range which cell do you want which range do you want to put up this is the range that you want to total up I selected it and how are you close my bracket let’s enter and Austin Martin the result is the total sum is 1 9 – 9 0 if I choose say Honda it will change to 1 and 0 6 4 0 if I choose Mercedes it changes again so this is summit formula so guys today we covered 10 most used formulas in Excel which you can also learn in even greater detail at my website my a lesson taught orgy and if you like this video do like click on this thumbs up button and if you have a question do mention it in the comments over here and I will surely reply to you and if you would like to subscribe to this channel then please do it will motivate me further create more such videos and also you will get notified and ever like sent out a new video on the web so hope you like this video until the next video this is your girl now signing off of the day how wonderful their head happy learning