How to use the SUMIF and SUMIFS Functions in Microsoft Excel for Beginners

hi there Jamie Keet here today at Teacher's  Tech hope you're having a great day today   today i want to show you how to use the sumif  and some ifs function inside microsoft excel   if you want to follow along with this  worksheet today i'll put the link down below   in the description where you can just click on it  and download it to your computer and follow along   now what i want to do first of all is just talk  about the sum function so just like this the sum   function will total up the column up above it or  the selected range now what's the difference if

you're new to this to using the sumif or some ifs  well what some if will do for you is it can look   through this uh all these different commissions  here sorry sales and based on a criteria that you   pick so let's say if i just wanted to add up  the north sales here and give that total back   i can do that with some if let me show you how  you do this so i'll just say what i want again   i want to be able to just look for the north  regions here and added those numbers up so   that's where i'm going to do a sum if because  i only have the one criteria of the north   so i'm just going to go ahead and start my  function my formula so i'm going to type in   sun and you can see sumif right there i'm going  to select it now if i follow along here what is   my range so the range that i want to select from  is right here so these the different directions   i'm going to highlight this now i'm going to  put a comma and it's asking me for the criteria   and this is where i can type in or select from a  cell reference so in this case i'll just type in   and i'll i'm going to type in north just like this  and then i'm going to put a comma in and this is   where it's going to be the sum range and what  is the range where i want things to add up from   right here so i can go ahead and close the bracket  and so what's going to give me back it's going to   add up the all the norths as a total sum so i'm  going to hit enter and i have 57 000. so just   to double check if i look up here at 20 000 it  should have chose at the 9000 would be 29 000   and add the 28 000 would give me 57 000. i do  want to point out just cell reference in this   example too so if i was going to go and do the  sum if again on this one and i'll just use a sub   r cell reference and rather than typing it  in so i go ahead and select my criteria here   comma and this time i'm just going to select the  cell remember if you type in anything it has to   be in the quotation marks you just can't type  it without those so i'm going to use that cell   reference though and then i'm going to put another  comma and then give my range again for the sum   and then just close it and i'm going to get the  same number there so go ahead and try those some

if now so one of the reasons why you'd want to use  your cell reference would be it makes it really   easy to make some quick changes to what you're  even asking the criteria from so instead of north   what happens if you want south well i could  just type south here and you can see how it   quickly pulls out the numbers for just south  on this one now let's move over to some   ifs and why would we use some if so sum ifs is for  multiple criteria in the case of some if it was   just the one thing i was looking at it was just  the sales region but what happens if i want to say   pull from the house here and how many new  customers to add that up so i'm going to   add up add in another criteria so i'm going to go  to this place right here and i'm just going to go   to equals and this time i'm going to put some ifs  and go from here and notice that this time it's   a little different it's asking for my sum range  first so this is my sum range i'm going to select   this because this is what it's going to be  looking to add from and i'm going to put a   comma and what is criteria what well my first  criteria is going to be from the house so i'm   going to highlight this right here and add a comma  what do i want it to look for and i'm not going to   use cell reference on this one i'm just going to  type it in so i'm going to use my quotation mark   and i'm going to type in lannister just like this  so i'm going to make sure i spell it correctly in   quotation marks and i'll put another comma what's  the other thing i want to compare it against well   new customers so i'm  going to highlight or select this range here and   comma and i want to look for anything greater than  10 new customers so we have to use the quotation   marks again so we add that in put the greater sign  in here and then 10 just like this and i'll end it   and my bracket and you can see 22 000. so  what it would look at the two different ones   so there's only two lannisters and this is the  only one right here that's above 10 because this   one right here is only four so look through two  different criteria let's try one more and adding   a third criteria so for this last example i'm just  going to be doing a sum ifs on the commission so   i wanted to add up certain commissions based  on the criteria i pick and i'm going to pick   three of them i'm going to go to this spot right  here to start my formula i'm going to put in my   equal sign then i'm going to type in some ifs  and continue from there so what is going to be   my the range that i'm going to be the sum range is  going to be this one and i'm going to hit my comma   and i'm going to go to my first criteria which  is right here new customers i want to look for   any of them above 20 new customers so  i'm going to go ahead and do my quotation   mark here so i'm going to put greater than  20 like this and close my quotation mark   give a comma what's my next criteria it's  asking for well in this case i'm going to say   i want to see anything above or equal to twenty  thousand so greater or equal than twenty thousand   i'm going to put my comma my quotation mark put  my greater than equals twenty thousand like so   another and then i got to make sure that i end  this and then comma and what's the last criteria   i'm going to look for well let's say it's going  to be based on region again and so i'm going to   highlight this region here and i am using i'm  just typing things in but you could use your   cell reference so i'm going to say north i'm going  to end it and hit enter and i get 4 800 4 800. so   what did it find it found this one from the north  here this 2000 and then this one from the north at   2 800 and both of them have 29 22 this is 20 000  or above and this one would match the criteria   so you could keep adding more criteria based on  it you just got to make sure you follow kind of   the operation of the formula that they're  helping you with so i hope this helped you with   your sumif and some ifs understanding  and that you can take this and use this   take a look at other function formulas that i have  in the playlist i'll put that down below thanks   for watching this week on teachers tech i'll see  you next time with more tech tips and tutorials