How to use COUNTIF and COUNTIFS Function in Microsoft Excel - For Beginners

hi there Jamie Keet here today at Teacher's Tech  hope you're having a great day today i want   to show you how to use the countif and countifs  functions inside microsoft excel so i'll be   showing you as a basic function and formula with  account if but i also want to show you how you   can add things like wild cards in and perform  operations with the countif and also how to   use multiple criteria with the countifs function  let's get started with this today on teachers tech if you want to follow along today  on this tutorial i'll put the link   to this worksheet down below in the description  and then you can click on it make a copy   or download it to your computer i if you notice  this is very similar to my some if and some   ifs worksheet so if you want to take a look at  that video to learn how that function works i'll   put a link to that below in the description in the  card up above and if you're looking to learn other   uh function and formulas inside microsoft excel  i'll put the link to that playlist so let's start   with the basics of count if and i'm just going to  start with a simple example here and that's with   this region code i'm just going to count up how  many of a certain number there using that function   when we start any formula we're going to  go ahead click in the cell where we want it

put our equal sign to start and i'm using the  countif so i'm just typing countif you can see   i have the option right here i'm going to go  ahead and select that now i said i'm going to   look at the region code i want to see how many  times 1 2 3 comes up so i go ahead and select   my range like this add a comma and now i can  say what i'm looking for when you're typing   that in you need to use your quotation marks so  i'm looking for one two three i end my quotation   marks and then i have to end my bracket here and  hit enter and you can see one two three comes up   four times here's the first one second one third  and fourth now i just want to point out cell   reference too so i'm going to go to this spot here  and i'm going to start my countif formula again   and you see there comes up and this time i just  want to use a cell reference so i'm going to go   ahead and use the same range it's going to be this  range right here that i want and i want to give a   cell reference where i'm going to type it in here  so notice there's nothing there right now but i'm   going to click on it and end it and hit return it  comes back as zero but now with the cell reference   i can quickly type something here that i'm looking  for so if i'm looking for a three four one i can   type three four one hit enter and it quickly gives  me the three back because three four one shows up   three times here and i can change this number very  quickly and get it to return so cell references   can make it easier all right let's go on to  our next example with countdf so that's just   with searching basic numbers how many times it  appears but i can also do things like greater than

or less than and i'll just give you a greater  than example so i'm going to go to start my   formula again with my equal sign  type in count if and it's right here and this time   my range is going to be in this one here so now  i'll say i want to look i could use different   operations i could use equal greater than less  than but i'm just using the greater than here   so i'm going to go ahead and put my comma  in and now it's asking for the criteria and   this is where i'm going to put my quotation mark  and i'm going to say greater uh greater than 15   000 here so 15 000 just like this and my quotation  mark and my other parenthesis and i'm going to hit   enter and now it finds five that are greater than  15 000 here so you can go ahead and count them   up one two three four five now i just want to  point out cell reference and i'm going to do this   again so if i go equals and type my count if  formula here make sure my fingers are in the right   place so count if and i go ahead and start it this  time so again my range is here just like this but   this time i'm going to reference this cell again  i haven't put anything in yet and i'm just going   to end it and hit enter you can see 0. but if  i want to find let's say anything greater than   10 000 now i can just type in my greater than  10 000 like this hit enter and it gives me back   seven that are greater than 10 000. so i can  use the cell reference for that also now i also   want to point out on our next example here i've  showed you with numbers but we can be looking up

different letters in here or words so if i go to this again and hit my equals and my count if and select that one and let's say we're looking in this one my range is here my comma if it's not a cell reference i have to use my quotes here so i'm let's say i'm looking for north then i type in what i'm looking for again spell it correctly and end it and my other bracket hit return and it can find three north in here again i'll leave this for you to practice but you can go ahead and type north in one of these spots and then use the cell reference

so you can even use operations with the  count if so if you want to add it up   multiple things in one column so in this case i  want to see how many one two threes and three four   ones i could add two different countables up so if  i go to this spot right now and use my equals and   i'm going to go to my count if here and this time  i'll go ahead select my range here just like this   and i'm going to just use and this time i'm not  using cell references but you could on this one so   i'm just going to put 1 2 3 just like this here  and end my quotation and then i'm going to put   in this bracket and i'm going to add  this time so i'm going to go ahead   put just the add sign and i'm going to do  another count if here so i'll go count if   and select this and what do i want well it's the  same range so i'm going to select this one again   put my comma what am i looking to add right here  so i'm going to use my quotation mark i'm going to   put 3 four one in and my quotation mark and now  i should be able to hit and it's gonna be seven   so it's added up to four one two threes and the  three four ones and it got a total of seven   you can perform other operations with this too  so if i just go back and i'm just going to make   a quick change to this so this was adding three  plus four if i go back over to here and just make   a change to this here i'm going to put multiply  so now it's going to multiply 3 times 4 and give   me 12. because it's not multiplying these numbers  it's multiplying the count of the two numbers that   i did so you could do this with different  operations if you're if that's what you're   looking for all right let's move over to wild  cards and i want to give you a couple examples

wild cards can be a great way to really speed up  what you're searching for and what they do is they   kind of they take the place of something and can  skip over something and look for something more   specifically that you want so for an example let's  give you a simple one and we're going to look at   this name list and i just want to see anybody that  starts with the letter a now if i go and start my   countif formula here and i want to see how many  people start with the letter a so my range is   going to be this inside here so i'll put my comma  give my quotation mark and i'm just going to put a   now i need to put the star after just  like this because that will say we'll   look for the a and whatever comes after does not  matter so i'm going to go ahead and my bracket   hit return and i get one so this look through  this list if anything started with an a it gave   me back one so let's just change this a little  bit and i'm just going to modify this formula so   i'm going to just take out the star and the a and  i'll type in ned so you can see if i hit return   it brings me back 0. there is a ned in here but  there's a ned stark and i don't want the stark   to matter in this i just want ned so if i go  right into here add my star like this hit return   you can see now it's got it's over to 1. all right  so what i want to do this time and i'm just going   to go ahead and just copy this formula here and go  over to this cell here i'm just going to paste it   in just like this so i'll just paste in the  formula you can see it's the same i'm going   to modify this one now at this case in this case i  actually want to look for last names and i want to   look for how many starks there are so notice that  in this case the at the net and star how it is if   i just double click in here i'm going to modify  it from here so if i go and actually put my star   first this time so i'm going to get rid of this  put my star first and now i'm going to type stark   like this and everything else can stay the  same make sure that this is inside your quotes   all right i'm gonna hit enter and it has three so  now it kind of bypassed the first letter here   in that star and now i could just write  stark and then it found me three starks   one two three so that's just some examples  to for wild cards let me show you one more   another example of a wildcard that you could  use would be a question mark so if i was going   to just type in i'll use the countif here and  this one over here i'll just use this right   here to kind of give a simple demonstration  this is my criteria here and if i go ahead and   pick what i want to be looking for so  the question mark so if i was looking for   uh the put in the question mark so i'm saying  it to ignore the first two spots and looking for   the third one so in this case if i was looking  for anything that had an h in there and ended it   it will give me one back if i quickly go  ahead and change this one if it's looking for   anything in with a c it gives me two spots so the  question marks uh here can tell you to skip over   uh a certain spot and then only look for the one  that you're left with and you can use different   numbers of question marks on it so the countifs  are used if you have multiple criteria if you're

looking through two different ranges and you could  do two or three or more i'm just going to show you   with an example of two criteria here and i'll go  to this cell right here and we'll start with our   equal and i'm going to type in countifs and what  i want to have happen this time i want to look for   anything above 22 in this column that will give  me back uh and compared to if it's there's a stark   there so i'm gonna have to use wild cards too so  my first is my range so i'm gonna go through here   highlight my first range and i'll put a comma and  what do i want to have happen so i'm going to put   my quotation marks and then i have to type  in what i want so i said greater than 22 here   and my quotation marks put another comma what's my  next criteria well this is going to be my criteria   here and so i give it another comma and  now i have to i'm going to use my wild   card in this here too so i'll put my equal  sign and i just want last name stark so   i'll put just like i showed you before put my  asterisks in there and i'm going to type stark   just like this and my quotation marks and my  bracket so i should get looking at this i can see   there's only going to be one above the 22 that is  a stark so i should get one back so i'll hit enter   and i get one back on this one so if i was gonna  if i saw if i change that number if i turn this   number to 21 hit return i get two back because now  there's another stark right up here that would fit   in what i'm looking for so let me give you one  more example how to use this multiple criteria   if we go equals this time i'm going to go so we'll  go count ifs again and i'm going to be looking at   anybody in the north with sales above we'll  say equal to or greater than 2 000 in this case   so i'm going to go to my first range here so  my first range i'll just start my formula here   and so this will be my first range and i'm going  to be looking for anything in the north so i'll   just go in tight north and i remember you can  use cell references but i'm typing and you have   to use the quotation mark what's my next criteria  well it's going to be right in here so i put my   comma again and what am i looking for so i use my  quotes again i'm looking for anything greater than   or equal to then we'll say 2000. so i'm going to  put in my 2000 here and in my quotation marks and   and my parentheses and i'll hit enter and i get  two so it was picking from this one right here   and we if we were looking for anything above  2000 from the north you can see this one would be   two this one's below so i can go back and adjust  these at any time if i was looking for a different   number so if it was going to be 2100  here it should drop by to one because this   wouldn't be in what i was looking for so remember  with multiple criteria i could add more criteria   to i would just keep filling it out so i hope you  liked this lesson today on how to use count if and   countifs for multiple criterion and how to use  the operation in wildcards inside it thanks for   watching this time on teachers tech i'll see you  next time with more text tips and tutorials