Counting Made fun - Source Image: / John Evans May 12 2006
Counting Made fun

There are many times when looking at a spreadsheet you need to know how many items there are in a certain section of how many rows are filled with data. Simply moving your finger across the screen and counting in your head is not a good way to go about it. Way back in time I wrote a tip on How to add up numbers in Excel. In this tip, I shall explain the Count functions. There are five distinct ones. I am just exploring two of the simpler ones Count and CountA.

Here is a little spreadsheet with words and numbers in it.

Without using your finger or pen to indicate the data, tell me how many cells have numbers and how many cells have words or numbers.

Too late Too slow!

Try these steps to create a formula that will tell you straight away.

In an empty cell type the words “How many numbers?”

In another cell type “How many cells with data in?”

Select the cell next to the first question.

Select the drop down arrow from the AutoSum tool in the Home tab.

Select the Count Numbers from the list.

Excel will automatically pick a selection for you. This may not be what you require so just select the cells you need. In this case A1:E6.

Hit the Enter Key and your result will show how many cells have numbers in.

Your answer should be 19.

Now select the cell next to the How many cells with data?

Select the drop down arrow by the AutoSum Tool and select the last entry in the list ‘More Functions…’

Follow my tip on Using Functions in Excel I wrote before to learn if you don’t know how to use the Insert functions Dialog box.

Locate in the list ‘ CountA’.

This is the function which counts the number of cells that are not empty in a range.

The A stands of all or anything. Even if the cells looks empty but someone has typed a space in it, CountA will count it as a cell with data in. 

Select the same area as before A1:E6

Your answer should be 28

From my screenshot of the Insert Functions you can see that there are 5 different count functions for you to play with. Enjoy yourself by playing with each one and see which one you will now be using a great deal because you now know how.


Please enter your comment!
Please enter your name here