Let Me Count The Ways
Elizabeth Browning had a way with words, it must be said. For today’s post, we’re going to borrow a line from one of her very best poems – we have already gone ahead and borrowed it, actually. It’s the title of this post, and a very relevant title it is too.
And why is it relevant? It is relevant because in today’s Excel tutorial, we’re going to count the number of ways we can count in Excel. You read that right – the number of ways we can count in Excel!
Turns out there is more than one way in which you can count in Excel – so let’s go ahead and learn how.
So how many people are there on that list? The no-brainer way of doing it would be to keep an index finger on the first name, narrow your eyes in focussed concentration, and begin a slow journey downwards. It might induce a migraine eventually, but it will get the job done. A far easier way (certainly on on your eyes!) is to select the entire range, and look at the bottom right of your screen. Excel obligingly provides you with the answer.
Or, if you have a formula fetish, you could use the first of our functions – the Count function.
Except whoops! If you hit Enter over there, Excel will stubbornly refuse to accept the fact that there are names in the range, and will return a value of “0″. This is because you can only use the Count function in ranges where you have numeric data – you’ll want to keep that in mind, because it has the power to save you a lot of heartburn!
So how do you count the names in there? Well, Excel, like we said, can count in many ways – and you can always use the COUNTA function. The COUNTA function counts only those cells in a range that are not empty. Since the cells above are expressly, well, not empty, COUNTA should return a value of 11 (the number of names in that range). Which it does!
There – that’s one mystery solved for the day. Having given ourselves a congratulatory pat on the back, we proceed to the next task at hand. Have a look-see at the data below.
It’s the same data, the same number of names, but with one important difference – there are blanks in between! Now, I may well know that the number of names is 11 – how do I find out how many blanks there are?
Microsoft Excel to the rescue once again – use the COUNTBLANK function. The COUNTBLANK function does exactly what it says – it counts the blanks.
And it will tell you, sure as day, that there are six blanks in that range. Mighty useful thing to know if you have thousands upon thousands of rows of data, wouldn’t you agree?
There – we’re done with the first part of our “Count” series. In the next post, we’ll describe two other members of the “Count” formula family – and they’re rather more powerful cousins, these two – so don’t forget to tune in!
And in the meantime, if you have any secret recipes to share when it comes to counting, don’t hesitate to let us know in the comments below. Happy counting!