Counts!

Now, some people have said that I am a bit “Batty”, but that’s another story. As we explored two years ago in this blog, there are several ways to Count (Count/Dracula/Bats, get it?...) your data in Excel.

It is important to know the uses and differences of the major types of Count Functions. In summary, they are:

1. COUNT: Simply counts the number of cells in a range that contain a number

2. COUNTA: Counts the number of non-blank cells in a range (including those with text)

3. COUNTBLANK: Counts the number of empty cells

4. COUNTIF: Flexible counting function (this can be a little tricky, so we will explore with some examples)

The COUNTIF function can be challenging to some users because of the prevalent use of quotation marks within the formulas. The following examples will demonstrate how it works (in each case, the formula uses a range named Data):

Count the number of cells that contain the word "James" (not case sensitive):
=COUNTIF(Data,"James")

Count the number of cells that contain the words "James" and “Suzanne”:
=COUNTIF(Data,"James") + COUNTIF(Data,"Suzanne")

Count the number of cells containing any text (ignoring the numbers):
=COUNTIF(Data,"*") Count the number of 3-letter words: =COUNTIF(Data,"???")

Count the number of cells containing text that begins with the letter "T":
=COUNTIF(Data, “T*”)

Count the number of cells that contain a value greater than or equal to 15:
=COUNTIF(Data,">=15")

Count the number of cells that contain a value from 5 to 20:
=COUNTIF(Data,">=5")-COUNTIF(data,">20")

This is, I might add, not the best tool to use in many cases. It does, however, come in handy frequently when you analysis requirements are not too demanding. I hope you have had a good week so far, and that you make your weekend, Count…

~ All the best ~