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 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
i want to compare it against 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 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 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 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