Excel Formulas and Functions - Tutorial Excel 2010, Excel 2013, Excel 2016 Using Excel Formulas

you now here we are in Microsoft Excel so here we have the start of a simple budget type of spreadsheet and the first one I want to show you is something that's called in although some now today we're going to cover a lot of different formulas and functions but first I might share the others some they'll show you what we call relative and absolute functions then I'm going to talk about how to do the very popular vlookup and the hlookup then we're gonna cover functions that are called some if count if and average if then I'll show you the popular if function along with different variations of how to did EF with the or and the and and then later on we'll cover some date functions at today and network days so notice how we have a simple budget spreadsheet going on a typical spreadsheet here's one that you're going to use all the time it's called although some obviously I want to sum up those numbers in column C by the way I'm using Excel 2016 but what I'm showing you should work in Excel 2013 Excel 2010 as well now sometimes I'll make my mouse do that so that you can find out where my mouse is so I just click them cell c11 obviously I want to get a total for that column so here's a classic one that's called autosum I'll click on the home menu up top home and we'll come way over here and pick in the word all there's some right there now when I pick on all this um usually it will capture the proper range for you in this case it did it says from see some c4 3 c10 whenever you see that : that means is it's a range of cells to where you read that is c4 through c10 and it is the right range the autosum doesn't always give you the proper range but many times it does I'm going to hit the enter key and now I'll have the sum for that column so watch again I'm going to delete that cell so I can do it again you click on the cell where you want your total to appear usually at the bottom of the column on the right side of a rail and on the home and you all come over here and I'll click on autosum and there is how it usually does gets the proper range for you I'm going to hit the enter key and now it has the mathematical some of those call of that column when I click in the cell notice up here in the formula bar has the formula as this equal sum open parenthesis c43 see 10 close parentheses if I need to change the formula I can change it up there in the formula bar now we'll do some other older sums as we go through now I'm going to come over here into the February column I want to start to show you something that's called a relative relative reference now let's say January was two real numbers but let's say February I want a 10% increase of the number that's in January so I'm going to type in all formulas of course start with the equal sign you probably know that so I'll type it equals and I'll click on this cell over here see 4 times 1.1 mathematically if you multiply any number by 1.1 it's going to give you a 10% increase so where's this see 4 times 1.1 that means use the number that's in cell c4 and multiplied by 1.1 when I eat the enter key it's a 13750 dollars now if I change this number over here in cell c4 both have been 14,000 when I give the Enter key two numbers are going to change this number over here is going to change and then cell d4 is also going to change because both of those cells have a formula that points back to cell c4 so when I hit the enter key notice how this number changed and this number also changed so think about that number fifteen thousand four hundred ten percent of the 14,000 would be 1400 add that again onto the 14,000 that's why it says 15 four so that's really a temperature increase now let me show you something that's called a relative reference this is going to be a big time-saver for you I want to have that same formula all the way down that column so I'm gonna get the black cross right here and we're gonna drag it down to the rest of the column that's called a speed fill it's also called an auto fill but let's see what happened to the formula it's more important the first one says see four times one point one that's the one I typed in the second one says see five times one point one the next one says see six times one point one notice how it's changed in the formula relative to the new cell that's called a relative formula that's going to see a lot of time I only had to type in the formula one time then I was able to get the black cross and drag it down all the way to the rest of the column when you see most people's larger spreadsheets it's hardly ever the case where they type in each formula one by one whenever they can that they use that they type in the top one and copy it down with the speed felt and then each each cell has the formula relative to that cell now this is c5 this one says c6 so that's called a relative formula now we're going to take that same concept and put that all the way across the the rest of the columns so this time I want to highlight those cells I'll get the black cross in the corner I'm going to drag it all the way over to the dry column now let's see what happen to the formulas this one says c4 times one point one this one says d4 times one point one this one says e4 times one point one notice how it's always the cell to the left of the current cell it's all a relative formula it changes that cell relative to that new cell and they'll save you a lot of time as you can see now once you get that concept you're going to use it over and over again for example over here when I first did it a couple minutes ago I did the autosum now I could click on this cell and do another auto sum and then click on this cell and do another order some but here's the quicker way we'll just do that speed fill notice right now the formula says some c4 3 c10 I'm going to get the black cross well drag it over now when we see the pound signs in our Excel spreadsheet that means we have to make that column bigger now I could do on one at a time so for example if I go over here notice for an analysis on between letter E and letter F and I get that black cross and I can double click there and that'll make that one column bigger here's how you can do them all at one time I'm going to start and letter F highlight all the way over to letter I now all of those columns are selected now move my mouse between those two columns and get the black cross double click and now because I had them all selected they all changed but let's see what happened to the formula remember how the first one said some C 4 3 C 10 then I get the black frost and drag that over the next one says some D 4 3 D 10 the next one says some efore through e 10 and so on so you start to use that relative reference it really saves you a lot of time because then you can just copy the formula over and it changes each one accordingly now I'm gonna go ahead and insert a call on the column a because I'll show you a couple more examples of the autosum here I'll type in the word average good now let's get the total for that row so notice how I'm on cell j4 and once again I'll pick on the word although some up here now notice how this time it gets the proper range it doesn't always get the proper range but many times it does now it says some c4 through I for which in this case is correct I'm going to hit the enter key so now I have the sum for that row now you can probably guess what I'm going to do now use a relative formula again by doing the order some I mean the autofill so I'll get the black cross and drag it down I thought that dragon all the way them to row 11 now here I have a row total for each row and I even have a grand total now this one is 0 because if you think about it it says some see 10 through J 10 there's nothing in those cells so it came to be zero now to me that zero is extremely so I'm going to delete it I'm gonna hit my delete key on my keyboard good now remember if you're watching we will have questions and answers at the end of the session so us you know keep you can type in your questions now and we'll get to those after the yeah as a little session so I did the autosum by picking on that cell and pick on the autosum up here in this case it gets the proper range then to get to the rest of them I clicked on that first autosum got the black cross dragged it down now at that point I got rid of the zero by deleting that cell but then basically we have a row total for each row and I even have a grand total now as well let me show you another way to use the autosum if I just took them here I want the average for sales now I'm going to come over here now you're going to click on the pulldown for the auto sum you see now you can do some other calculations sum average count minimum and maximum so I'm picking the word average now this time I want you to notice that it did not pick the proper range it wants to average from c4 to j4 but if I included j4 in the average then the average will not be correct so here's what you do you're going to highlight the range that you want to do so I'm gonna highlight from a c4 over to I for UC then I'll hit the enter key and now I have the average for that round watch again I'm going to delete that cell so you can redo it I'll click in the pulldown where it says although some right there and I'll pick in the word average notice how it did not pick the proper range that time so you're going to highlight the range that you want to do I'm going to highlight it from c4 to i4 then I'll hit the enter key and that's the cell that I want to hit the average from now you know what I'm going to do from here I'm going to copy that down so I'll get the black cross in that cell drag it down and now we have the average for the rest of the race as well excellent now before I continue I want to make sure I format all those numbers I wouldn't turn it in like this because if you notice some of the numbers have two decimal places this one even has three I think it's so long with three somewhere but we want to make that look a little bit better so I'm gonna highlight all of these numbers it's not all the numbers yeah if you look at these numbers down here they have three decimal places so I highlight all the numbers now you probably know about formatting I'm gonna go ahead and click on this pulldown where's his number on the home and the home toolbar and let's see we're gonna go with currency format see now it looks a whole lot better now they have dollar signs commas two decimal places so you always had a bunch of numbers then here you see some number formatting options under the under the Home menu now if you'll be using yourself for a long time that guy have then you know another way to format your cells is called format cells for example I'll right-click on those cells and then up again the word format cells this is the way I learned a long time ago and I still use that screen to Thursday you know so here you have your number formats but in this screen you can go a little bit further you change the decimal places and the dollar symbol and I mean the currency symbol and then you would even pick a format for your negative numbers so I'll click on OK but either way is fine you can highlight your numbers and find some formatting over here under the Home menu or if you right click on those cells that you can go back to format cells good now when I was able to click on this cell and get the black cross and drag it down that's called a relative reference let me show you this one says average see for 2i4 distance is average c5 to i-5 that's just as c6 to i6 that's called a relative reference but sometimes you don't want that to happen sometimes you want to use what called the absolute references for those of you that have used Excel before this is where you have the dollar sign in your formula let's see in the example I'd like to do the percent of total the percent of total is going to be taken by take by calculating the total for that row and dividing it by the grand total now first I'm going to do this formula the incorrect way I'll show you why it's incorrect and then we'll correct it but this is the trap that many people fall into I'm going to type in the equal sign I'm going to click on the total for that row which is j4 I'll type in the slash for division and I'll click on this cell over here with this je level which is the grand total so the formula says equals J 4 divided by j xi I'm going to hit the enter key now previously I had format that column as a percent so I'm just trying to make sure the Arbour sense I'm just gonna highlight that column and we'll make it percentage format okay good so watch what's gonna happen now you would think that you'd just be able to copy that down like I've been showing you I'm gonna get the black cross copy it down and notice how if they say division by zero so let's see what happened this is what I'm trying to show you this one says J 4 / j 11 the next one down says j 5 / j 12 you see it's still a relative formula well there's nothing in J 12 that's why it says division by zero but on a 1 / J 12 on 1 / j xi the next one downs getting even further away now it says J 6 / J 13 except there's nothing in j13 as far as division by zero but on a 1 / J 13 I want / j 11 so sometime somehow we have to tell it that when we copy that formula down not to change the J 11 part so what I just showed you was the incorrect way to do this formula but this is the way this is the trap that most people fall into so let's go ahead and fix that now I can also double click on that cell to see the formula I'm going to double click on it now I'm going to put a dollar sign before the J and a dollar sign before the 11 let's take a closer look at that so now you can really see that a little bit better it says J 4 divided by dollar sign J dollar sign 11 when we see the dollar signs in the formula we are not talking about money it makes it what we call an absolute reference which means when I copy down the j11 part that form will still say J 11 but you have to see it understand what I'm talking about I'm gonna hit the enter key da now the dollar signs are not gonna make a difference until I recopy down so I'm gonna click on that cell will get the black cross drag it down now we have actual numbers let's see what happened this one says J 4 / j 11 the next one down says J 5 but it's still dividing it by J 11 because of the dollar signs it's called an absolute reference the next one down says J 6 but it's still dividing it by j 11 the first part that formula is relative in other words the next one will say J 7 next we'll say J but notice how they're all dividing it by J 11 because of those dollar signs that's called an absolute reference so those come up in your larger spreadsheet so you can see the difference that it made let's see another example of that I had to see this a couple times before I go I got it over here I'm going to type in the word tax rate now I'm recording this from Philadelphia Pennsylvania in the United States and here our Patrick is actually in Philadelphia is actually 8% it's pretty high okay so I'll pay with 8% there now here I'm going to type in the word sales tax and let me show you another example of the absolute reference I'll type in the equal sign all formulas always start at the equal sign I'll click on the total for that row I'll do multiplication which is the asterisk and I'll click in the 8% which is M 2 now I know I want to make that into an absolute reference you can either type in the dollar signs or a quicker way is a keyboard shortcut I'm going to hit the f4 function key f4 and those head up with the dollar signs in there for me so you can either type in the dollar signs or when you're on that part of the form that you want to make an absolute reference you can use the f4 function key enough it's the dollar signs in there for me when I hit the enter key now we're going to copy that down we'll get the black frost and drag it down let's see what happened this one says J for as multiplying about em - this was taken J 5 but it's still multiplying by m2 because of the dollar signs this one says j6 multiplying about m2 so the first part that formula is relative it is changing for each cell those into our abstruse sj7 the second part of the formula is absolute it doesn't change because of the dollar signs so hopefully you saw how to use the dollar signs in your formulas it's called an absolute reference but whereas the normal ones are called relative if I change that 8 percent let's say I make it 6% watch those numbers in column n they're all going to change because they were all pointing to that same cell now let's put it back to 8% and watch the role change again as those are called relative references now let me I'm gonna zoom out good the next thing I want to show you is something that's called a V lookup the very popular function I'm going to go to the next sheet down here this is called orders now this might be a list of all the orders that your company has notice here I have a customer ID but notice how it doesn't show the company name if I come down here to the sheet that's called customers then here we have the customer ID the company name and all the information about the customer so somehow I have to pull the company name for the specific customer ID into the sheet that's called orders well that's what we're going to use a vlookup the vlookup has a few different purposes but probably the main purpose of the vlookup is to look up information from one sheet into another sheet let's see if we can make it work now I'm going to go ahead and insert a new column a column C I'm going to right click on letter C and I'll click on insert good so in this case I'll type in company need that'll just be the column heading now I'm going to slightly formulate the equal sign of course now follow my mouse and I'm going to pin this FX right there the FX is called insert function and it's a very powerful window it's really going to show you all of your built-in functions that come with Excel now there's over 300 of them every time they add a new version to excel they're always that a new functions so you can either search for them up here or you can click on this pulldown and then you can have a bunch of functions as you can see there's both different categories now we're going to see some of those categories today now I happen to know that the vlookup is under the lookup and reference category if you didn't know that you would type in the word vlookup up there and search for it but I'm gonna pick on the word lookup and reference now I'm going to scroll down on this column and you see the bottom one is called vlookup now it's going to give you a brief description and after our session here you can always go back and pick on help on this function but I want to show you how to use it right now of course so I'll pick on the read vlookup and I'll click on OK this screen is called function arguments where it's also called the function parameters so what do I need to make this function work this needs four pieces of information or four function arguments the lookup value is what you're trying to look up I'm trying to look up that temir ID so I'm going to pick on cell b2 ID that's what I'm trying to look up so lookup value now the table array usually is on a different sheet it doesn't have to be on a different sheet but a lot of times it is now a better name for the lookup excuse me the about name for the table array is the lookup table so I'm going to click on that area when I come over here I'll go to the customers sheet you see my mouse down here at the bottom and customers click there and we're going to highlight did now usually I would recommend that you highlight the entire column and a workout a whole lot better I'm gonna highlight from column a all the way over to column F and that'll be our table array or the lookup table now the lookup table can be many many columns across and many many rows down and certainly it can be on a different sheet like it is now where sometimes it's on the same she does the original data the most important part about the lookup table is the first column that's what has to match what you're actually trying to look up we were trying to find it a customer ID notice how the first column of the table is the customer ID so that's very important it doesn't have to be column a necessarily it just works that way this time it just has to be the first column of the table that you select otherwise you can go many many columns across so if you're looking at for part numbers then the first column of the table has to be part numbers if you're looking for pío numbers then the first column of the table has to be Pio numbers and then from there you can get many columns across now let me show you how you calculate the next part of the vlookup which is called the column index number you count from the left side of the table so in the table this is column one two three four five and six I want the company name the company name is the second column one two so for that reason I'm going to type in the number two for the index number that means when I find the appropriate row based on the customer ID I want the second column of information which is going to be the company name now I'm going to come down to here and pick on the range lookup the range lookup let's see what it says the range lookup is a logical value that means it's going to say the word true or the word false to find the closest match in the first column then you put the word true there to find an exact match you type in the word false probably like eight out of ten times if not more you'll use the word false in the vlookup now in a little while I'll show you under has the word true there but many times we use the word false if you're looking for a specific part number you want that exact number right so usually will type in foster let's see what we have the lookup value is cell b2 which is right over here the customer ID the table array or the lookup table is on the other sheet this is customer sheet column a through column F column index number says two that means here on balcony of the table array that'll be the second column information which read the company name if I wanted the contact name then the contact column index number would be three because that would be the third column over but here I wanted to come be mean that's the second column on the table so that's why I have the number two right there and then the word false means I want to do an exact match I'm gonna go ahead and click on OK now I'm going to make column C bigger so it looks like if looked up that customer ID on the other sheet and returned the proper company name take a look at the formula up here it says equals vlookup open parenthesis b2 comma customers exclamation point A through F that's the lookup table comma two that's the second column com false that means who want to do an exact match and then close the parentheses now the way I built that I built that with the FX over there although after a while you can just kind of type it in once you've used that a few times you can just type it in but there's nothing on with these new FX I still use it to this day now let's make sure it's found the right one I wouldn't assume that it's the right one until you check it out so the customer ID is VI n ET and you can see the company name let's go to the customer sheet now these happen to be in alphabetical order so if I scroll down then we'll see there it is on row 86 VI n ET and it looks like I've found the proper company name so let's go back to the other sheet so that's like a textbook example of when they use the vlookup if you want to pull information from another sheet the vlookup is perfect for that now let's go ahead and copy that down I'm gonna get the black cross again right there now let me show you a shortcut for the black cross sometimes you can double click on the black cross if you double click on it it'll go all the way down now the reason that that worked is because I had something to my immediate left over here or something in the column to the immediate right if column D was not filled in and column B was not filled in then the double click of the black cross would not have worked but in this case all's I did was I clicked in that cell got the black cross and I double clicked and I went all the way down now I have the appropriate company name for each different customer ID because of the vlookup now while I click on this vlookup knows house has the word false at the end that means it has to do it in the exact match let me show you what that means I'm gonna come over here and I know I don't have one four five of these so it had been vvvvv and notice how it says n/a n/a means not available or not found it says that because I was looking for an exact match it didn't find an exact match so it says a well while we're talking about these functions I know some people say well I don't want that to say na I want to say something different so here's a bonus that I'll decide to throw in for you right now we can use something that's called if our watch my screen and we're zoom in for you again good I'm going to come up to the formula and I'm going to go right after the equal sign between the equal sign and the letter V and okay have been if ever now the if error is a function within itself so it needs its own parentheses so I'll do an open parenthesis we're going to go to the end of the whole thing I'll type in comma now the word not found here has to be in quotes because it's a piece of text and many of these formulas when you have a piece of text you have to put the text in quotes so the open quotes not found closed quotes closed parentheses so let's take a look at the whole thing it says equals if our open parentheses then we had the whole vlookup that was there before after the vlookup I have comma open quotes not found close quote close parenthesis I'm gonna hit the enter key notice now it says not found all right so the if our can work with any formula what it really means is if the vlookup gives me an error then put the word not found out if the vlookup does not give me an error then just give you the normal value of the vlookup now we're going to copy that all the way down I get the black cross and double click that so now if I came over here and I typed in CC see that I guess is not found in this case I'm going to undo that so about that one will be back here's undo and then for this one I'll type in the IND T and now it finds that one again now notice how this vlookup says the word false at the end and then I added an if error in there as well but sometimes the vehicles the word true let me give you one of those examples I'm going to go back to this sheet that's called V lookup the sheet itself is called vlookup okay now this time the the lookup table is within the same sheet might as well zoom in again alright now this time I'm trying to find the appropriate I'm trying to find the appropriate tax rate for the income level so in this case I'll type in 40,000 for the income level and there is how we get 31% let me get back there again okay so now we have 31 percent now let's take a look at the vlookup it says b2 that's the income level now the lookup table is what I have in blue over here d2 through f7 d23 f7 is to lookup table the number 3 means when I find the appropriate row on white when I get the third column 1 2 3 that's why it says 31 percent now notice how the fourth parameter of the vlookup is not fair usually you would expected the city where true or false but notice how this time the fourth parameter is not there the fourth parameter is actually optional let me show you why it's optional I'm going to pin the FX right there and now we can see the function arguments again now the ones that are in dark black like that those are required and the ones that are in light black like this one are optional so that actually has a purpose when you're looking at the screen the ones that are in dark black are required and the ones that are in light black are optional so let's see what range lookup says range lookup is a logical value to find the closest match in the first column you would put true there or if you will admit it it assumes that it's true all right so when the range lookup says that weren't true or if you leave the range lookup blank then it is that is true which means it's going to find the closest match we find the exact match will type in false okay so let's see what happened if that's the lookup said the word false at the end then this would say na right now but we've just discovered that when the fourth parameter is not there it as soon as it is true which means we're going to find we're 40,000 what this 40,000 fall within these is between the 27 3 + 58 158 5 so everything between 32 27 3 and 55 is going to give us this tax rate of 31% so when the fourth parameter is not there well if the 4th primer says that were true that means you're going to match the range or when the vlookup says the word false it'll have the word it'll be an exact match for example let's type in 60,000 down now this is 36% if you think about it the 60,000 falls between 258 5 and the 1 31 8 so everything between 55 and 131 8 will give us the 3rd column which is 36% now let's say the number that you type in here is bigger than the largest number in the table well just like in the tax table it doesn't matter how far they're in the highest tax bracket you're still going to get the highest tax rate so I'm going to type in a big number like 450,000 and notice now that gives us the highest tax rate of 45 55 % so I showed you a vlookup over here let's go back to the other sheet I showed you if you look up there has the word false at the end and that's when we want to do an exact match I showed you the if error that can work with any kind of formula when you have an error if you want to have a better message then I should do the vlookup that has the true as the fourth parameter over in this other workbook as we can say scooter all right now let's see a similar one that's called H lookup let me go to the next sheet it's called H lookup let me make sure well we'll do this one I make sure we catch up with our slides the H look up is similar to the V lookup except this time notice how the table is running across the screen it's horizontal whereas the other one the table was running down the screen on this one it was vertical so the V actually means vertical and the H actually means horizontal so it all depends on how the table is running across the screen but if I click here notice how the H look up is going to be very similar to the V lookup except that the table is kind of on its side the lookup value is B 2 which is the salary over here then the the table reference is a II 1 3j3 what I have in a in light blue over here but this time instead of looking at the first column it's going to match the first row of the table that's the big difference then the number 3 says when I find the appropriate column I want to go 3 rows down which is going to be the percent the tax rate and then once again even with this one if I go back to the F X notice how the same thing with the fourth parameter if it's not there it assumes that it's going to be a true so it's going to assume that it's going to match the range when the fourth parameters not filled am just like the vlookup so in this case the 21 55 66 gave us 28% because let's see where that false that falls between 26 51 and 27 301 everything between those two numbers we'll get the 28% as you can say so the H lookup is very similar to the vlookup except the table is now going horizontal and so that what the vlookup to take a piece a maybe seven out of 10 lookups that you'll see will probably be a V lookup but sometimes you'll run across when it's called each look up alright now let's go on to a new topic and there's how I'm going to go to a new sheet down here that is called some F countif and average F let me delete this column for it now would ever need that okay so now clearly I want to get the count of all the breakfast items if I use the count function the count function counts everything I just want to count the ones that are that are breakfast only so watch what we'll do I'll type in the equal sign once again I'm going to pick up this FX over here and we'll be back to the insert function window and I'll pick the word count this time I'm searching for it and then I'll pick the word go and it finds different ones that have to do with a count so I'll pick on the word count if and look what it says count the numbers of cells within a range that me the given condition of course you have to the session you can always go to the help window and see more information about that but I'm going to show you how to use it right now so I'll pick on count if I pick on okay so the range here is going to be the full list of data which in this case is column F when I how to column F now the criteria is what you're looking for and this is where I'll pick on their word breakfast all right so I want to go through column F and find all the breakfast items and that's exactly what count if is going to do for us I'm going to click on OK let's see what happened now we have the number 16 take a look at the formula up here zoom in for you this says equals count if open parentheses f : f which is where all the data is column F comma I to which is the word breakfast and then we got the number 16 so that means if we went through column F we would see 16 items to say the word breakfast now watch what I'll do I can quickly get the for the other categories I'm going to get the black for us right there I'll double click on the black cross and then it goes all the way down so that quickly I have account on all the categories so when you have a big list of data like this the account if can be especially helpful now I'd like to do a sum of all the breakfast items so let's try this one now it'll use something that's called sum if a pep in the equal sign and I'll go back to the FX again which is called insert function okay now this time I'll type the words sum I'm going to click a go notice now there's one that's called sum if and I'll pick an okay the summit has three function arguments but it's going to be the first two of the other one anyway the range is still column F so I'll highlight column F the criteria is stilled the word breakfast which is i2 now the sum range means what number do I want to add up for all the breakfast items I want to add up the numbers in column e so I'll have a column e the range is column F that's where all the data is the criteria is i2 that's what I'm looking for and the sum range is column E that's the numbers I want to sum up when I find the breakfast items I'm going to click on OK now I'll take a look at this formula this is equals sum if open parentheses f : F comma I 2 comma e : e close parenthesis now I'm going to copy that right on down I'll get the black cross that will click on the black cross and now I have the sums for all the other categories you can see how these would be extremely beneficial on a large list of data now I have all the counts of the categories and all the sums are the categories as well because of count and some F now there's also a very similar one that's called average F I want to get the average of the categories so I'll type in equals once you can all go back to the FX this time I'll type in the word average click on go and there we see the average if right there alright so I'll pick an average F and click on OK now the range this time is still going to be column F the criteria is still going to be the word breakfast which is cell I to now the average range is maybe the same as the sum range which would be the numbers in column E so for the range I have column F for the criteria I have I to and for the average range of column E so this means I want to take the average of all the breakfast items I'll click OK and now we have a number there take a look at your formula this is equals average if open parentheses f : F comma I 2 comma e : e close parenthesis now let's go ahead and copy that down of course I'll get the black cross that will click on the black cross and now it goes all the way down now I have the average of all the categories now I wouldn't leave it like that I'd like to format those columns take a look better so I'm going to highlight column K and column L will come up here and we'll pick on the currency format it once again currency now looks a whole lot better but some people ask you what's the difference between currency and accounting format notice how the dollar signs are right next to the numbers right now that that's currency format well let me show you what accounting does that I click on that pulldown I'll pick on an accounting format notice how the dollar signs are left justified so maybe you like it that way better so whichever way that you like is fine I just want to show you a difference let's make it about the currency and notice how the dollar signs is now directly next to the number whereas in accounting and make the dollar signs left justified in that cell now in this case over here I want to type into our country and I'll typically France and this time I just got copy France all the way down now there's a few more functions that go along with these there's countifs some ifs and average ifs so when I have an S at the end of the function then I can do more than one criteria so let's try something like that account of expense type for France it's the column title I can make that larger of course so we'll type in the equal sign and this time I'll go back to the FX and this time else had been count if pick on go let me try countifs with an SDN good and now there's countifs and i'll pick on okay all right so the whole point of the of the countifs is to add more than one condition the criteria range is still column F criteria one is still the word breakfast which is cell I to notice now it's going to have a second criteria range you really have as many as you wanted to so for the second criteria range I'm going to highlight column B and for the second criteria I'll pin the word France over here which is m2 notice how I can go for a third criteria range and a fourth and smitty as you wanted to but doesn't make the point I'm trying to make so the criteria range the first place I was looking for is column F the first thing I was looking for was breakfast which is cell I to the second place I was looking was the country's and column B as the second criteria range and the second thing I was looking for is the word France in column M so it has to be France and it has to be breakfast at the same time when I click on okay so it has the number four there's sixteen total breakfast items but out of those on a four are also from France take a look at the function up here it's a Z equals countifs open parenthesis F called an F comma I two comma B : B comma M 2 close parenthesis so it has to be France and breakfast at the same time I'm going to copy that right on down and some of them are lower numbered like here is 0 so there was no France items that are also other at the same time so there's a count else let's do one more that this would be some ifs I'll say the sum of expense type for France and of course I can make that beggar good so I'll type of the equal sign this time we're going to use when it's called some ifs so I'll come up here and pick up the FX and I'll type in some ifs and you can see it right there all right so I'll pick on some it's like okay all right the sum range is what we want to add up we want to add up so this time to some ranges first well how to column e criteria range one is still column F that's where I'm looking criteria one is still the word breakfast user cell I to notice the some ifs is going to have more than one criteria range so criteria range two is still going to be the country in column B and criteria two will be the word France in column M the difference here is the some range that you need to for the some ifs now that's at the top so how to column e for that column F is the first criteria range I to is the first criteria which is the word breakfast column B is the second criteria ring and then m2 is the word France that's what I'm looking for so it has to be France and breakfast the same time this time I want us to a sum and you can see the formula up here equals sum ifs open parenthesis e call an e comma F : f comma I 2 comma B colon B comma M 2 close parenthesis now of course we're going to copy that down now I have the sums for all the categories that are also France at the same time now what I'll do is I'll pick in the formulas menu up top formulas then you come over here and you say show formulas write down and notice how everything except