- 11 minutes ago
Category
✨
PeopleTranscript
00:00In this Excel tutorial we're going to look at a couple of functions that I hope that you'll find to
00:05be useful.
00:05And they are count and count A.
00:08In addition, we will also talk a little bit about the status bar and how you might use it.
00:14So you can see here that I have a spreadsheet that's pretty big.
00:19There are many different records here, hundreds and hundreds of records.
00:24And remember that that's what we call each of these rows, basically.
00:27A row that is filled in with data is a record.
00:30This spreadsheet has hundreds of records and what if I would like to know exactly how many there are?
00:37Well, of course, I could just scroll down quite a ways and look at the data and just keep going
00:44down until I find a blank row.
00:46But after a while that gets harder and harder to do and less effective.
00:50So let's learn how to set up a formula using the function count or count A to help you to
00:56count the number of records that you have.
00:58And to do this, I'm going to browse over to the right and find an empty cell.
01:03And here's an empty cell.
01:05That's the one I'm going to use.
01:06Now, of course, you could do this in any number of other places.
01:09This is just the example that I'm showing.
01:11So here in this cell, I'd like it to tell me how many total records are in this Excel spreadsheet.
01:17First thing I need to do after selecting the cell is tap an equals sign.
01:21If you've watched my previous Excel tutorials, you know that when using formulas in Excel, you can think of it
01:27kind of like an algebra equation.
01:29And so I'm going to say this cell equals.
01:33So I tap the equals sign.
01:34And then I'm going to type in the word count, left parenthesis.
01:38And then what do I want it to count?
01:41Let's say I want it to count the total number of records that have units sold.
01:46I'll just click on column E.
01:48It highlights the entire column.
01:50And you can see here at the right that it's put E through E, which in effect means the entire
01:56column.
01:57And then at this point, I should put in the closed parenthesis.
02:00But you don't really have to do that.
02:02So I'll just tap Enter on the keyboard.
02:04And it tells me that I have 700 records in this spreadsheet that have a number of units sold.
02:11Now, of course, I could go up here and I could just type in something like total records with sales.
02:17So I could label that column and what it means.
02:21Now, what if instead of having it count the total number of records with units sold,
02:26what if I would have said count, left parenthesis, and had it just count the first column here?
02:32Right?
02:33Every one of these records, I would assume, has something in the first column.
02:37So if I would have selected column A and tapped Enter, take a look at the number that it returns.
02:43It returns a zero.
02:44So why is that?
02:45Why did it return 700 when I counted units sold, but it returned zero when I counted segment?
02:52The reason why is because the count function that I'm using here, that only counts cells in that range that
03:00have a number in them.
03:01And so because my column A does not have any numbers, it's just words.
03:06It's government, mid-market, channel partners.
03:09These are words only, no numbers.
03:11So because of that, they aren't counted and it returns a number of zero.
03:15So let's see how you could fix that.
03:17If you want to count the total number of records, but you want it to include text, this is what
03:23you would have to do.
03:24This cell equals count and then add the letter A.
03:29And to be honest, I'm not sure what that A signifies.
03:32Maybe it just means letters, not just numbers, but letters.
03:36Or maybe it signifies all, count all.
03:39But anyway, count A, left parenthesis, and now if I select column A, the entire column, tap enter on the
03:49keyboard, look at the number it returns.
03:51It returns for me 701.
03:54Now let's browse down the page a little bit and take a look at why it said 701.
04:00You can see because I chose count A instead of just count, it included this first cell, cell A1, in
04:10the tally.
04:10And so that's how it came up with 701.
04:13When I simply used count with column E, if you remember, it counted all of the cells that had numbers
04:20only.
04:21And so units sold was not included in that tally.
04:25Therefore, the end result was just 700.
04:28So I hope that you can see some ways that count and count A could be useful to you.
04:34Before we end, though, I want to also point out that there is a much faster way to get similar
04:39information.
04:40Although sometimes you will just want to use count A and count.
04:44So it's important to know those.
04:46But let's say just at the spur of the moment, you need to know how many records are here in
04:51column H.
04:52All you have to do, without setting up a formula at all, is just click on the column H, and
04:58look what it does.
04:59Down here on what we call the status bar, we have some automatic information that's brought to us by Excel.
05:06And one of those bits of information is the count.
05:09So it tells me right there, there's a count of 701 records in column H.
05:15It also gives me the sum of all of the numbers that are in column H.
05:20And so that's a huge sum that I have there.
05:22And it gives me the average of the numbers in column H.
05:26That's with zero work on my part.
05:29There's no formula.
05:30There's no functions used.
05:32I simply just click on the column letter, J, K, and it gives me that information.
05:37Now what if I pick a column that has only words, like column A or B?
05:43Then it can't give me as much data, but it does give me accounts still.
05:47Just so you know, you can customize the information that it does give you.
05:50You can right-click on the status bar, and it gives you the opportunity to make some changes to the
05:57information that's brought to you.
05:58I'm going to click here on sale price to illustrate that a little bit better.
06:02I can now right-click, and I could add minimum and maximum.
06:07And so now I can look at this and see the cheapest thing that's for sale in this spreadsheet sells
06:13for $7,
06:14and the most expensive sells for $350.
06:19So in some cases, this status bar will give you all the information you need,
06:24and you won't need to create a count or count A formula.
06:28In other cases, you will need to.
06:30I hope that you found this tutorial to be helpful.
06:32If you did, please click the like button below,
06:35and consider connecting with me on my social media websites like Facebook, Pinterest, and Twitter.
06:39And definitely do subscribe to my YouTube channel for more videos about technology for teachers and students,
06:45and watch for another video from me at least every Monday.
06:48If you'd like to support my YouTube channel,
06:50please consider becoming a patron of mine through my Patreon account,
06:54and you'll find information about that in the description below.
Comments