Simple statistics: counting numbers

If you want to know how many numbers are in a given group of cells, there’s an easy way to do it: use the count() function.

Imagine, for a moment, that you have a list of 100 dates. The question is — are they all numbers? It matters because if they’re numbers, you can use a variety of functions to extract the year, month, and day. If they’re not numbers, then to extract the year, month, and day you have to use text-manipulation functions such as right(), left(), and middle().

So you can take your group of dates, and use count() on them. If the dates were in a1 through a100, the formula would be =count(a1:a100). If the result is one, they’re all numbers. If the result is zero, they’re all text. If it’s somewhere in between you’ve got a mixed of numbers and text, which you may be able to separate by sorting it. It may ask you if you want to sort text that looks like numbers as if it were numbers, and if you say no then the two formats will end up separated.

Count() will also let you check more than one range at a time. You could count all the numbers in column A and all the numbers in column B in the same function. It’s worth noting, however, that if two ranges overlap, then you’ll be counting any numbers in the overlapping section twice. For example, if you counted all the numbers in column A, and all the numbers in row 1, then if cell A1 had a number in it then your result would have one more number than is actually there.

All that said, let’s take a look at what does and does not count as a number. In the tables below I’m going to give you a variety of things, showing both the actual contents of the cell (the formula), and what you see if you’re not inside the cell, showing the formula (the result).

Contents Appearance Notes
=1 1
=5/2 2.5
=7+9 16
=today() 5/27/12 This returns today’s date, as a number.
=pi() 3.14159 Remember geometry? area=π r2? pi() returns π
8717044007104620 8.717E+15 This is scientific notation — very helpful when you’ve got a whole lot of digits and only care about the most significant ones. It’s essentially saying 8.171 * 10^15.
Contents Appearance Notes
‘3 3 Note the ‘ instead of the =. That makes it a character, even if it’s a character that normally represents a number.
‘Three Three The spelled-out names of numbers are not numbers.
1 2 3 4 5 6 Pretty much, if it has characters that are neither numbers, part of a function name, or mathematical operators, it’s not a number. This even counts spaces.
=left(“05/27/12”,2) 05 “05”, since it was extracted from text, using a text manipulation function, is still text.

Simple statistics: Counting

One thing that people often want to know, especially if they’re storing lists of things in spreadsheets, is “how many” there are of something.

Fortunately, there are a fair number of ways to get a spreadsheet to tell you how many it has of something. Unfortunately this means it can be pretty confusing when trying to pick the right one for your needs. So, here’s a quick guide as to what functions are commonly available, and what they do. In the next few weeks I’ll talk about them in more detail, including how they overlap and how to emulate one with another. Then I’ll give some examples of how you might use them in the workplace or in daily life. Continue reading Simple statistics: Counting