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 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 i'm going to go 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 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
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 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 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 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 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 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 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 text tips and tutorials