Let's take a look at Excel's Count, CountA,
and CountBlank functions
and when each of these can come in handy. This is the start file for this section.
Let's jump to the first tab called count.
Here we have a small dataset with names of employees, their entry dates and a date here is missing, their
yearly salary, and their previous year's salary. We are going to see what the difference between these three
functions are down here.
So let's start off with count.
Open the bracket.
Now I can either count different ranges separately or I can just give it one range.
In this case I'm just going to reference all the cells above, close the bracket.
Now when I press enter what do you think I'm going to get?
Did you get zero?
Why does Excel do this?
Well, the Count function does not count text.
It only counts numbers. When I pull it here.
What do you think I'm going to get as the answer?
7.
It's counting all of these dates.
Dates are in fact numbers but this one here,
That's not a number.
It's text so it doesn't count it.
So now when I pull it here, what do you think I'm going to get? 8.
These are all numbers and let's put one more time. Six.
I have 6 numbers.
There are two empty cells here and it doesn't count these.
Now what if you actually wanted to count these.
You have to use another function that's called the "CountA" function. "CountA" count numbers and text.
So now I'm going to reference these, close bracket.
What do you think I'm going to get? 8.
So let's just pull this across.
What do you think I'm going to get here?
Also 8, because it counts
text and numbers. When I pull it here it's going to be 8.
Right?
Because these are all numbers. Now here I'm going to get 6 because "CountA" doesn't count blank cells.
Now let's get to our last one.
We have "CountBlank" .
We're going to give it the same range, close bracket, press control+enter just to stay on this cell.
No blank cells here right? Our count is zero. Here we're also gonna get zero.
And here what are we gonna get in that last one?
We're gonna get a 2 because we have 2 blank cells here.
Now if I delete something here.
So if I delete the text in cell A9 what's going to change below? My "CountA" changed and my "CountBlank"
changed.
Now let's say if someone sent you a file.
It was a large file and you want to make sure you don't have any blanks in there.
You can run a check by using the "CountBlank" function if you want to make sure that you don't have any
text in there.
You can run a test by using the "CountA" function and deducting the count function from it.
So if I just write this in one go I would say "CountA" and count these because that counts everything, right?
Text and numbers minus count of these, close bracket.
Press enter, I get one. So you could write your own validations to make sure that your dataset passes
certain checks.
Now of course you can also add data validation to the cells to ensure that people don't type in text
where they're supposed to type in a date but it's always possible that someone can go and just delete
your validation so it's a good idea to run some checks like these to make sure the data pass some basic checks.
In the next lecture
we're going to take a look at how we can count based on criteria.