Cell Counting Techniques!

It is frequently handy to be able to count certain types of cells in a worksheet range. The following are four common Excel Functions that accomplish this quite efficiently:

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 (that we will explore with some examples)

So how can I use the COUNTIF function, you might be asking. For a bit of Inspiration, the following are some examples (in each case, the formula uses a range named Data):

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

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

Count the number of cells containing any text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 4-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")

Being able to count cells on your terms is a great tool to have in your Excel Tool Kit. Especially the versatile COUNTIF! (I could say, “Make your spreadsheets Count”, but that would be way too corny…).

Cheers!