Microsoft Excel 2013 Tutorial - 25 - Cool Counting Functions Video Lecture | Microsoft Excel 2013: Be the Excel Master - Software Development

what up what up I hope you guys are
ready to learn about counting functions
functions well as a congratulations you
heard the worst song ever made by human
being thank you thank you thank you
everyone all right
so let's learn about counting functions
what the heck's accounting function well
it is basically a way to count cells now
we already know that if you select just
a group of cells and it's going to tell
you how many years you selected down
here says 60 cells see average counts um
if you see a bunch of cool stuff down
there and you can actually I don't know
so you make a selection and I'm going to
name this beef ham because why the heck
not and you can actually use a formula
account to come on I think you're going
to auto fill beef ham for me beef ham
all right something like that so count
pretty much just counts cells simple
enough now what I'm going to be showing
you guys how to do is pretty much more
advanced count functions something like
okay we have a selection what I want you
to do is count the number of cells whose
value is greater than 2 but less than 6
and you can also do cool stuff like
count the blank cells and I'll show you
guys that so um let's make a new we'll
just make it right here all right so any
time you want to camp count I can't say
that any tiny one can a blank number of
cells you have selected you can do count
blank now you can make any selection
like this you see how I have most data
select about three blank ones are also
selected we'll check this out I'll hit
enter and it says 3 so that's how many
blank cells it counted now the number of
non blank cells if you ever want to
count that is count a
I actually don't know why they named it
count aaaa COUNTA okay there's my ghetto
Enosh for the thing alright for the
tutorial but check this out
say that we have this selection we have
four blank cells in eight non blank
cells pretty much a cell with any type
of data and all whatsoever
and that's why COUNTA if anyone knows
why it's named count up please let me
know um I don't know maybe just some
thug was making these functions but
anyways it gives you eight as your
answer now you know how at the beginning
of the tutorial I said that if you ever
just want to count the number of cells
you have selected then you can use the
formula count and it's going to do for
you however I have to admit that was a
lie because check this out I mean we I
already showed you this once it'll work
perfectly so let me just show you again
you know just for kicks okay count okay
one two three four and I'll select these
four two so we have eight cells selected
what the heck is going on I had eight
cells selected and it's giving me the
answer for and the reason for that is
the formula count or the function count
as you can see that only counts numeric
cells so if you have something like this
selected it's going to give you four
because this doesn't count is in numeric
so it's kind of annoying but I don't
know that's the way Excel works and it's
kind of weird because Excel arm it
doesn't have any function built-in just
to count the total number of cells you
have selected I don't actually don't
know why they didn't include that but
say um sometimes you actually do need to
do that how do you accomplish this task
well let's just go ahead and make any
selection and make sure you include a
range of numeric cells and non numeric
cells and give it a nice name like um
meatball with a Z so meatballs
so now this selection or wherever I
selected it is named meatballs so just
remember that so anytime you want to get
the total number of cell selected this
is what you have to do equal sign rose
and what this is going to do is all
right so this function right here it
gives you the total number of rows you
have selected now of course this isn't
the total number of cells so just in
this case 1 2 3 4 5 6 7 8 9 so it'll
just give you the answer of 9 well
that's not what we want so what we need
to do is we actually need to multiply
that by columns so my selection was
named meatballs and it gives you the
answer of 36 times 9 or CV since 9 times
4 equals 36 so again why Excel just
didn't make um I don't know they should
actually name like count all cells or
make a function like that but instead if
you ever want to get all of the cells
you have selected of course whenever
you're doing grid math rows x columns
equals total area essentially or in this
case total amount of cells so that
ladies and gentlemen is how you do that
and do I have time to talk to you guys
about a few more things I think so all
right so another function that's
actually probably the first useful one
is this say that you wanted to find the
number of cells in the selection who's
pretty much equal to a certain value so
I'll say that I have this selection
right here and I'll give it a name I'll
say that this is the name of um I don't
know swimming medals so we'll say this
is a swim medals hit enter so anytime
you want to say ok we have the selection
and we want to I don't know maybe find
how many cells in the selection
contained the number 8 what we can do is
this
set it equal to count if and the it
needs two pieces of data the first piece
of data it needs is what selection now
we can either just selects um a
selection with our cursor or if you have
a name selection you can throw that in
so swimming medals that's the selection
I want you to search and the other
parameter that needs is what do you want
me to search for well let's just search
for the number 8
so what we're pretty much asking is how
many cells in this selection contain the
number 8 and there are 3 cells
fascinating and actually I can find them
1 2 3 so again that's count if and you
pretty much ask it a question how many
in this selection contain 8 in order to
give your answer for you counts it right
every single time so this is getting
pretty long and I don't know I have a
short attention span so I'm going to go
like chase a hamster around or do
whatever people with short attention
spans do so thanks for watching don't
forget to subscribe and I'll see you
guys the next video