Hi, this is Wayne again with a topic “Using Count and CountA in Excel – Excel Tutorial”.
In this Excel tutorial, we’re gon na look at a couple of functions that I hope that you’ll find to be useful and they are count and count a in addition. We will also talk a little bit about the status bar and how you might use it. So you can see here that I have a spreadsheet, that’s pretty big! There are many different records here, hundreds and hundreds of records, and remember that that’s what we call each of these rows. Basically, a row that is filled in with data is a record. This spreadsheet has hundreds of records and what, if I would like to know exactly how many there are well, of course, I could just scroll down quite a ways and look at the data and just keep going down until I find a blank row. But after a while that gets harder and harder to do and less effective, so let’s learn how to set up a formula using the function count or count a to help you to count the number of records that you have and to do this, I’m gon na Browse over to the right and find an empty cell and here’s an empty cell, that’s the one I’m gon na use.
Now, of course, you could do this in any number of other places. This is just the example that I’m showing so here in this cell. I’D like it to tell me how many total records are in this Excel spreadsheet.
First thing I need to do after selecting the cell is tap an equal sign. If you’ve watched my previous Excel tutorials, you know that when using formulas in Excel, you can think of it kind of like an algebra equation, and so I’m gon na say this cell equals. So I tap the equals sign and then I’m gon na type. In the word count left parenthesis and then what do I want it to count? Let’S say I want it to count the total number of records that have units sold I’ll, just click on column E.
It highlights the entire column – and you can see here at the right that it’s put e through E, which in effect means the entire column and then at this point I should put in the closed parenthesis. But you don’t really have to do that. So I’ll.
Just tap enter on the keyboard and it tells me that I have 700 records in this spreadsheet that have a number of units sold now, of course, I could go up here and I could just type in something like total records with sales, so I could label That column and what it means now, what if, instead of having it count the total number of records with units sold? What, if I would have said, count left parenthesis and had it just count the first column here right every one of these records, I would assume, has something in the first column. So if I would have selected column a and tapped enter take a look at the number that it returns. It returns a zero. So why is that? Why did it return 700 when I counted units sold, but it returns zero when I counted segment? The reason why is because the count function that I’m using here that only counts cells in that range that have a number in them, and so because my column, a does not have any numbers, it’s just words: its government mid-market channel partners. These are words only know numbers so because of that they aren’t counted and it returns a number of zero.
So, let’s see how you could fix that if you want to count the total number of Records, but you want it to include text. This is what you would have to do. This cell equals count and then add the letter A and, to be honest, I’m not sure what that a signifies. Maybe it just means letters, not just numbers, but letters or maybe it signifies all count all, but anyway count a left parenthesis and now, if I select column, a the entire column tap enter on the keyboard, look at the number it returns it returns.
For me, 701. Now, let’s browse down the page a little bit and take a look at Wyatt said 701. You can see because I chose count a instead of just count.
It included this first cell cell a1 in the tally, and so that’s how it came up with 701. When I simply used count with column E, if you remember it counted all of the cells that had numbers only and so units sold was not included in that tally. Therefore, the end result was seven hundred, so I hope that you can see some ways that count and count a could be useful to you before we end, though, I want to also point out that there is a much faster way to get similar information, although sometimes You will just want to use count a and count, so it’s important to know those, but let’s say just at the spur of the moment. You need to know how many records are here in column H. All you have to do without setting up a formula at all is just click on the column, H and look what it does down here on what we call the status bar. We have some automatic information, that’s brought to us by Excel, and one of those bits of information is the count. So it tells me right there there’s a count of 701 records in column H. It also gives me the sum of all of the numbers that are in column H, and so that’s a huge sum that I have there and it gives me the average of the numbers in column. H, that’s with zero work on my part, there’s no formula.
There’S no functions used. I simply just click on the column letter, J, ok, and it gives me that information now. What if I pick a column that has only words like column, A or B, then it can’t give me as much data, but it does give me a count still just so. You know you can customize the information that it does give you you can right.
Click on the status bar and it gives you the opportunity to make some changes to the information. That’S brought to you, I’m going to click here on sale price. To illustrate that a little bit better. I can now right click and I could add minimum and maximum, and so now I can look at this and see the cheapest thing.
That’S for sale in the spreadsheet sells for $ 7 and the most expensive sells for 350 dollars. So, in some cases, this status bar will give you all the information you need and you won’t need to create account or count a formula in other cases you will need to. I hope that you found this tutorial to be helpful. If you did, please click the like button below and consider connecting with me on my social media web sites like Facebook, Pinterest and Twitter, and definitely do subscribe to YouTube channel for more videos about technology for teachers and students and watch for another video.
From me. At least every Monday, if you’d like to support my youtube channel, please consider becoming a patron of mine through my patreon account and you’ll find information about that in the description below .