Welcome to Office 2013, video number 21,
Excel Basics video number three.
Hey, in this video, we want to talk about counting and adding.
Now, I've already downloaded the workbook.
If we go over to our web page, Video Class Project 21,
there's the workbook you want to download.
And if you were in class yesterday,
I've changed the workbook.
So if you're watching this video over the weekend,
you've got to re-download the video.
Hey, we need to learn how to count and add.
Now many of us know how to count and add.
But in this video we want to see how
to either add numbers or count items, not only all
the items in the column, but sometimes we want just some
of them.
So if I wanted to count how many sales rep Gigi there were,
I do not want all of the records, or the whole column.
I just need to count four.
If I was adding all of the sales for Gigi,
don't want to use the sum function
and add the whole column.
I want to add just the ones that belong to Gigi.
So in this video, we're going to see
how to count, add, and then count and add
with a criteria or condition.
All right, let's talk about the Count function first.
Count, C-O-U-N-T, and as we've seen in this class,
there are some cool drop downs for functions.
And there's a little screen tip that
tells you what the function does.
Count counts numbers.
So I can simply highlight.
If you highlight a bunch of text, it just gives you zero.
Because it's not programmed to count anything
except for numbers.
So Count counts numbers.
And there's nine records here.
Now sometimes you have a column of names
or other type of data that's not a number.
So you can't use the count function.
In that case, we can use COUNTA.
Now COUNTA, if you read this screen tip,
it counts cells that are not empty.
That means COUNTA will count anything,
whether it's a number, or word, or error, or anything.
However, for words, this is a great function to use, brrrp.
I'm going to highlight.
So in this case, of course, we could use Count here.
But sometimes, you're not given numbers.
You're given text, names, something like that.
And you want to count.
So the COUNTA will look through all of these words
and count nine of them.
Now two counting, right?
We counted numbers.
We counted text items with the COUNTA function.
Adding, well, we know the Sum function.
And similar to Count, if you were to try and add
a bunch of words, it just says, hey, there's zero, right?
So that's not going to work.
However, if you put into the number argument, numbers
it will add them.
And it will add all of them.
So the total for this column is 2,400.
Now we want to talk about adding and counting
with a condition or a criteria.
Let's first talk about the COUNTIFs.
Now, they named this function nicely, right?
Count, well, I don't want to count all the item.
I want to count the items if they're equal to Gigi.
Now there's S, just means you can do one or more conditions
or criteria.
So right now, we want to see how to use the COUNTIFs
function to count, not all the records, but just
the ones that belong to Gigi, so COUNTIFs.
Now there's a COUNTIF that's an earlier function.
But the ones with the S since 2007,
and that's a long time ago, are a little bit more
polite and easier to use.
So we're going to use the ones with the S,
even if we have just a single condition.
Now the nice thing about the screen tips as it tells you
criteria range, and then please give me the criteria.
All right, so criteria range, well, this
is just like the COUNTA.
We just highlight all of them.
All right?
Well, right now COUNTIFs wouldn't know what to do.
Because comma, you have to tell which ones.
Remember, counting not all the items, just some of them.
In order for COUNTIFs to know which one of those to count,
you have to give it criteria.
So we're going to click on Gigi.
And now it'll do its thing.
It will look through that whole column and say,
is that equal to Gigi?
False.
Is that equal to Gigi?
True.
And it will continue down counting four.
Now later in this class, we'll come back
and we'll see how to add more arguments.
You can have lots of criteria here.
It will count four.
Now what if someone told you, well, you
have a bunch of dates here.
And I just want to count how many transactions
were on 10/23.
Well, if we used Count and counted all of them, dates
or numbers, right, it would give me all of them.
But that's not what I want.
I want to use Count.
And there's a bunch of cool functions.
Count counts numbers.
COUNTA counts not empty cells.
COUNTBLANK, I mean, sometimes you want to count empty cells?
Yeah, sometimes you want to count empty cells.
Ah, but COUNTIFs, I'm going to double click that one.
Hey, criteria range, remember if we're using Count or COUNTA,
for this matter, because none of these cells are empty,
we would enter into the array, brrp, all of them.
But we don't want to count all of them.
So what do we do?
We use COUNTIFs, enter the range, comma,
and tell it what the condition or the criteria is.
So I click on that date.
It will instantly go through.
Are you equal to 10/23?
True.
Are you equal to 10/23?
True.
Are you equal to 10/23?
False.
It will go all the way through and count
exactly how many dates are 10/23.
Exactly four.
Now we can come up here.
Let's delete this Gigi and type 10/23/2013.
And so we can see one, two, three, four.
There's four of them.
So in both of these cases, we use COUNTIFs to not
count all of them, but just some of them.
Now, let's talk about adding.
Well, we could do the same thing.
If we wanted to add all of Gigi's, if I come up here
and delete this and type Gigi, I could do it manually, right?
I could say 200.
And watch this, I'm going to use my control trick
to select cells not next to each other, click and drag.
Now here's a great trick.
You can actually, in Excel, the status bar we saw in Word,
the status bar showed like the page number.
But look at this.
If you highlight numbers in Excel or cells,
it will show you calculation results.
The status bar is programmed to show
you some basic calculations.
So what's the total right there?
The sum is 1,100.
All right.
So how did it do that?
Well, we didn't add up all of the numbers, just some of them.
Now here's the trick.
Because we're going to do the SUMIFs formula here.
Well we, of course, are going to have
to give SUMIFs the whole column of numbers.
But we're also, inside the SUMIFs function,
going to have to give it the whole column of names.
And then tell SUMIFs only get the Gigi ones, all right.
So are you ready?
Equal SUMIF, and SUMIF is an earlier function,
it's existed for a long time, but it only does one condition.
SUMIFs does one or more.
And the reason we use the SUMIFs is
because it's a little bit easier to use.
So even if you're using one condition, SUMIFs.
And the reason why is the screen tip,
the names of the arguments, are much easier to understand.
When it says SUMRANGE, remember sum means adding.
So whenever you see SUMRANGE, hey, those are the numbers.
So I'm going to highlight all the numbers.
Now comma, criteria range, well we need to add only Gigis.
Oh, well then the SUMIFs in the criteria range,
one argument needs to look at all of the names.
Now notice these are both the same size.
Comma, and then the criteria.
Well, somehow SUMIFs needs to look through here
and know which ones.
So SUMIFs in the criteria one, we
give it the condition or the criteria.
OK, close parentheses.
Control Enter.
And sure enough, it went through and got only Gigi's sales
numbers, and then added them.
How did SUMIFs do it?
It actually took this criteria, Gigi,
and went through each name in the Sales Rep column and asked,
are you equal to Gigi?
False.
So it didn't do anything with that number.
Are you equal to Gigi?
True.
So it took that number and remembered it.
Are you equal to Gigi?
False, so it didn't do anything with that number.
Every time it did not find a match,
it didn't use the number.
But when it found a match, in this case, one, two, three,
it took each one of these numbers.
So it used this column to match criteria.
But it took the number from this column and added them.
Hey, let's look at another adding with a condition
example.
Now, we want to add the numbers from this column,
but only when we find a date in the date column
that matches 10/23/2013.
Now let's go ahead and change this up here.
So we get a visual there.
It'll help us while we're learning.
All right, so I'm going to come over to G13 and use the SUMIFs,
equals SUMIFs.
Now, we have some arguments here in our screen
tip that we have to enter, sum range.
Hey, that just means, those are the numbers we want to add,
brrp, comma, criteria range.
Well, I don't want to add all of them.
So I want to add just the ones that fall on 10/23.
So I have to tell SUMIFs, hey, the criteria range, well that's
all the dates, same size, comma.
And since we don't want to add all of them,
we've got to tell SUMIFs what the criteria is.
Hey, it's 10/23, close parentheses, Control Enter.
600 bucks, and I can even add this up.
There's three, and there's three right there.
How did it do it?
Again, SUMIFs had to take this criteria
and find matches in this column.
Any time it found a match, it took the number
from this column.
Any time it did not find a match,
it didn't use the number.
So it didn't add all of them, just the ones on 10/23.
That is pretty amazing, SUMIFs to add with one condition.
Hey, in this video, we saw how to count and add.
We saw how to use the Count function if we wanted
to count all the numbers, COUNTA if we wanted
to count all the text items in a column,
and the Sum function to add all of the numbers in a column.
But we don't always want to count or add all of the items.
Sometimes we want to add or count just some of them.
So we learned about the COUNTIFs function.
That allows you to count with one or more condition
or criteria.
COUNTIFs, it was great for counting just Gigi
in this column.
We also saw how to add, not all of them,
but just some of the numbers using SUMIFs.
That's when you want add with one or more condition
or criteria.
We saw how to use SUMIFs to add from the sales column
all of Gigi's sales.
All right.
We'll see you next video.