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.
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!
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.
- In cell C3, write “=COUNT(“ (without parenthesis)
- Now select the cells from A1 to A10
- Use “)” to close the formula which becomes “=COUNT(A1:A10)”
- 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
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.
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.
COUNTA function will also count the cells with Error value (like #N/A).
Excel COUNTBLANK function is opposite to COUNTA function. It counts the number of empty cells in the array or range.
Syntax of COUNTBLANK
Let’s count the number of blank cells in the same example.
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!
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.