Section: Excel Functions Tutorial: The COUNTBLANK Function
How to Use the COUNTBLANK Function of Excel
Some of the things that make Microsoft Excel so robust are its built-in functions. In this tutorial we discuss the syntax and usage of the COUNTBLANK function and provide spreadsheet examples to clearly explain its use.
to learn more. ○
The COUNTBLANK function counts the number of empty or blank cells in a specified range. Unlike other counting functions, the COUNTBLANK function only allows one argument.
Bạn đang xem: How to Use the COUNTBLANK Function of Excel
What is the COUNTBLANK Function?
► range specifies a single block or group of contiguous cells in which you want to count the empty or blank cells.
A KeynoteSupport.com Tutorial
Cells containing empty text strings (zero-length strings) are counted. An example of an empty string, the result of an
, is shown in cell B5 in the worksheet below.
Cells that contain the number zero are not counted. However, cells containing spaces entered with the space bar ARE counted (See “Properly Deleting Cell Contents in Excel” below).
A KeynoteSupport.com Tutorial
Examples of the COUNTBLANK Function
Let’s look at three COUNTBLANK examples.
Example 1: =COUNTBLANK(A1:A5) – This function asks Excel to count the number of empty cells A1:A5. There is one empty cell: A4. Remember, zero is not counted.
Example 2: =COUNTBLANK(A1:B4) – This function asks Excel to count the number of empty cells in the range A1:B4. (Contiguous cells that form a rectangle can be referenced by the upper left and lower right cells.) The two empty cells are A4 and B2.
Example 3: =COUNTBLANK(A1:B5) – This function asks Excel to count the number of empty cells in the range A1:B5. Cell B5 contains an
(shown in D6) that results in an empty string. The three empty cells are A4, B2, and B5.
To ensure you won’t have mistakes in your Excel worksheets when using counting functions, see the next section that discusses the right and wrong way to delete cell data.
Properly Deleting Cell Contents in Excel
After selecting a cell, its contents can be visually erased by pressing the Delete key or the Space bar. Always use the Delete key to delete cell values in a spreadsheet!
Why? Because a space is considered a value by Excel! Look at the worksheet below.
We deleted the contents of cell A2 using the delete key. Then, in cell C1, we asked Excel to tell us the number of blank cells in A1 through A2. Excel returns a value of 1, which is what we would expect.
Now look at cell B2. We deleted the contents of cell B2 using the space bar. And in cell C2 we ask Excel to tell us the number of blank cells in B1 through B2 and Excel returns a value of 0 (zero)!
Don’t forget that Excel considers a space entered into a cell by the keyboard’s spacebar to be a value in the COUNTBLANK function and creates problems with other Excel function. Always us the delete key to delete data in a worksheet cell.
Always delete cell contents using the delete key, NOT the space bar.
To count all cells in a given range that are not empty, use
the COUNTA function
locking any cell that contains a formula
and then protecting the worksheet to prevent accidentally erasing the formula.
When creating complicated functions and formulas, it is helpful to type them into a Notepad file, and then copy and paste them into the cell. Do not use a word processing program such as Microsoft Word.
Lastly, don’t forget to start each function with an equal = sign unless it is inside another function.
We hope this article on how to use the COUNTBLANK function of Excel has been helpful. Cheers!
Return to the top
Chuyên mục: Kiến thức