Fabulous Examples of Excel Functions:
COUNT, COUNTA, and COUNTBLANK

Microsoft Excel has many built-in functions. In this tutorial, we discuss COUNT, COUNTA, and COUNTBLANK. We provide many examples and worksheet images to help explain the proper use of these functions.

Author: Patricia Lynn

Don't miss our related tutorials, Microsoft Excel Functions: COUNTIF Basics and for a discussion of advanced COUNTIF topics, Microsoft Excel Functions: COUNTIF Advanced.

Click on a link below to go directly to the function:

• COUNT
• COUNTA
• COUNTBLANK

COUNT

The COUNT function calculates the number of cells in a specified range that contain numeric values. The syntax of the COUNT function is:

COUNT(value1,value2,value3, ... )

… where value1, value2, etc. are from 1 to 255 arguments, specifying a cell range or array.

What counts? Numbers, dates, or text representation of numbers. Empty cells, logical values, and error values are ignored. (To count logical values, and error values as well, use the COUNTA function).

• Example 1:  This Excel function counts all cells that contain numeric values in cells A1 to A5. Excel returns a value of 3 (cells A2, A3, and A5)
• Example 2:  The number of cells with numeric values in cells in A1 to A5 is 3, plus 1 for the number 10; so Excel displays 4 (cells A2, A3, A5, and the number 10)
• Example 3:  This Excel function counts all cells containing numeric values in cells A1 to A5, and cells B1 to B5. Excel returns a value of 5 (cells A2, A3, A5, B2, and B4)

COUNTA

The COUNTA function calculates the number of cells that are not empty. The cells may contain any kind of data. The syntax of the COUNTA function is:

COUNTA(value1,value2,value3, ... )

… where value1, value2, etc. are from 1 to 255 arguments, specifying a range of cells or an array.

Use the sample worksheet above, and notice how the values change by using the COUNTA function instead:

• =COUNTA(A1:A5)  This function counts all non-empty cells in cells A1 to cell A5. Excel will return a value of 4
• =COUNTA(A1:A5,10)  The number of non-empty cells in A1 through A5 is 4, plus 1 for the number 10, equals 5
• =COUNTA(A1:A5,B1:B5)  This function counts all non-empty cells in cells A1 to A5, and cells B1 to B5. Excel will return a value of 9

COUNTBLANK

The COUNTBLANK function counts the number of empty or blank cells in a specified range. A distinction between this function and the previous two functions is that the COUNTBLANK function only allows one argument. The syntax of the COUNTBLANK function is:

COUNTBLANK(value1)

… where value1 specifies a contiguous range of cells or an array. Multiple arguments are not allowed.

Cells that contain zero values are not considered empty, so they are not counted. But if a cell contains a null value that was calculated from an IF formula, it will be counted. Let's look at the following examples:

Final Thoughts

It is advisable to lock any cell containing a function or formula, and then protect the worksheet, to prevent accidental erasure. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.

When creating complicated functions and formulas, it is often helpful to type them into a Notepad file, and then copy and paste them into the cell. Notepad is a simple editor that comes free with Windows. Do NOT copy functions and formulas from Microsoft Word or other word processing programs.

Don't forget that every Excel function must begin with an equal = sign unless the function is imbedded inside of another function.We hope this article has been helpful. Cheers!

   Back to Top