Excel COUNT, COUNTA & COUNTBLANK Functions

One of the common tasks associated with data is to do counting. MS excel comes with a number of built-in functions which can do this task for you exactly as per your requirement. Normally, you want to count the number of cells with data or based on some condition(s). In this article, we will learn about basic Excel Count Functions and how to effectively select and use the more appropriate one. So what is the difference among COUNT, COUNTA and COUNTBLANK functions and how to use these?

In fact, if you just need to count the number of cells with data, you don’t need to use any formula. Just select the cells and average, count and sum are automatically displayed in the status bar if you are using Excel 2007 or above. For Excel 2003 or earlier, you can right click on the status bar and select any one of average, count or sum.

Count Function

Unlike the name suggests; Count Function does not count the number of cells with data. It counts the number of cells which contain numbers. It will ignore any text, reference, blank cell, blank text etc. and will return the count of cells with numbers only. Next time when you are using COUNT formula, be careful!

COUNT Formula

The syntax for count formula is really simple.

COUNT(value1, value2, value3, …)

Here value1, value2, etc. are arguments and can be either values or cell references.

Suppose we need to count the cells with numbers in cells A1 to A10 in cell C3.

Count Function in Excel

Using COUNT Function

  1. In cell C3, write “=COUNT(“ (without parenthesis)
  2. Now select the cells from A1 to A10
  3. Use “)” to close the formula which becomes “=COUNT(A1:A10)
  4. Press ENTER to get the result.

If we want to count the cells with numbers in Cells A1 to A10 and B2 to B15, the formula will be

=COUNT(A1:A10, B2:B15)

What if the cell contains a DATE? In excel, it will be considered as number and will be taken into account. Try to insert a date in a blank cell to see the result.

Counting a cell with date with count function

Count Function will count a cell with Date

COUNTA Function

What if you want to get the count of cells with any type of data (non-empty) which you see in status bar? Well, here we use COUNTA function. COUNTA function will count any cell which is non blank.

Syntax of COUNTA Function

The syntax of COUNTA formula is similar to that of COUNT formula.

COUNTA(value1, value2, value3, …)  

Here also the arguments can be either the values or reference of cells.

Let’s use the same data table to see the result with COUNTA function.

=COUNTA(A1:A10) returns 7 counting all non-empty cells.

Excel COUNTA Function

COUNTA function to count the number of non-blank cells

COUNTA function will also count the cells with Error value (like #N/A).

COUNTBLANK Function

Excel COUNTBLANK function is opposite to COUNTA function. It counts the number of empty cells in the array or range.

Syntax of COUNTBLANK

COUNTBLANK(range)

Let’s count the number of blank cells in the same example.

COUNTBLANK(A1:A10)

COUNTBLANK Function in Excel

Counting the blank Cells

Note however that unlike the syntax of COUNT and COUNTA functions, COUNTBLANK takes only one argument. So, COUNTBLANK(A1:A10, B2:B15) will not work!

Conclusion

These are three basic count functions in excel which helps you count the number of cells with numbers, any data and blank cells. For counting number of cells with condition, we can use COUNTIF or COUNTIFs functions.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>