Join Dennis Taylor for an in-depth discussion in this video Use COUNT, COUNTA, and the status bar, part of Excel: Advanced Formulas and Functions.
- [Instructor] We're looking at a worksheet called countcounta in our Chapter Five exercise file. They're two separate functions. Count and counta. They're very similar, and yet it can be confusing at times, because also there's this Status Bar entry called count. Status Bar is at the bottom of the screen under the Sheet tabs. Anytime you highlight two or more cells with values, you will see some information at the bottom of the screen. You might see as many as six different statistical measures. If you don't, right-click and in this list, this huge list here, consider checking all six of these. Of different times you might change your mind, if you never need some of these, you could uncheck them certainly. But at the moment, they're all checked. Let's focus what happens here as we click for example, an entire column. I click column C. What do we see here at the bottom of the screen? Let's say we're not concerned with the average of the sum, although we certainly could in some cases there. We've got a maximum and a minimum. But we see the number count 100. What does that mean? 100 cells in this list here in column C have data in them. Numerical count 99. It's pretty obvious C1 is not a number. So we're counting the cells that have numbers with this phrase numerical count. Now, there's a function called count. This is referred to in column H. We see count. Count of cells containing numerical data. That's what the word count means when we use this function. So if I click column C here. And Enter. There are 99 cells there that have numerical data in them. When we click column C and look the bottom of the screen, we see that phrase numerical count 99. But to the left of that we see the word count 100. So here in the Status Bar, count means the number of cells that have data, but the function count means the number of cells that have numerical information. Another function is called counta. And this counts cells that have data. As I click column C here. Going to be seeing 100. 100 cells there have data. And we see the description here. We see in cell H2 counta. What does that do? Count of cells containing data. But that's the same as the Status Bar entry for count. So notice the dissimilarity here in the use of these. Here's the word count, meaning one thing as a function, meaning something different in the Status Bar. It can get confusing at times. Let's use this in a different sense in column D. Now, you sharp-eyed observers there probably are seeing something a little bit off. For the moment I won't mention it. But I'm clicking column D. What do we see at the bottom of the screen? 93 cells have data. 91 have numbers. What does that suggest? One of these cells is not a number. And perhaps you've seen it already. This is not a number. It's either a lower-case l or a capital I. It's not a number, though. We could jump out momentarily, use this function, isnumber, and if it's not, and it isn't here, we see false. By the way, its companion is called istext. It's covered in a later movie. In this case that would be true. Back again. After clicking column D, we look at the bottom of the screen, count 93. Numerical count 91. Now, both of these functions are valuable. They both have their place. Once again, count, counta. We see the difference. It makes perfect sense based on the context of what we're using here. And yet there is that momentary bit of confusion at times if we're looking at entries in the Status Bar. With no question, at different times as you're working with Excel, using either the functions or that capability of the Status Bar to give us information about cells, these are valuable tools, yet at times confusing as we work with Excel.
- Displaying and highlighting formulas
- Debugging formulas
- Creating 3D formulas
- Creating nested IF functions
- Exploring VLOOKUP, MATCH, and INDEX
- Tabulating data with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding values with statistical functions
- Adjusting results with rounding functions
- Converting values between measuring systems
- Calculating dates
- Returning reference data
- Manipulating text
- Extracting information