May 23 2007

COUNTA and COUNTBLANK Formulas in Excel

When using Excel, there will be many instances where you will want to count a particular range of cells. Excel has numerous ways built in already to count that range for you. One way we already explored is through the CountIf Formula, and also the Count Formula. Excel also features the CountA and CountBlank Formulas to make your counting even easier. Let’s go ahead and take a look at each of the formulas:



Both COUNTA and COUNTBLANK can accept individual objects or a Range of Cells. The difference between the two is what they are made to count. COUNTA counts the number of cells that are not empty and returns the number of cells containing some form of data. COUNTBLANK, on the other hand, will count the number of empty cells in the range and return the total (the opposite of COUNTA). Next let’s take a look at some examples.


CountA CountBlank Example

Example 1: =counta(A2:A10) will return 8 since there are 8 cells in the range that are not blank.

Example 2: = countblank(C2:C10) will return 4 since there are 4 blanks within that range of cells. Counta and Countblank basically perform opposite actions: Counta counts non blanks and CountBlank only counts the blanks.

Example 3: = count(B2:B10) will return 7 since there are 7 cells in the range that contain numbers. In this example Counta and Count will return the same result since every cell in the range that isn’t blank contains a number. In the next example you will see what happens when count is used on non-number cells.

Example 4: =count(A2:A10) will return 0 since non of the cells in the range contain numbers.

In all the example, we used a very small sample set to explore the uses of each of the count functions. When you begin using these functions for larger sets of data you will immediately realize their benefit and potential. Please contact me if you have any further questions on these formulas or would like further explanation.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 


Comments are closed.

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks