Kiến thức

How to Use the COUNTBLANK Function of Excel

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.

fun intro image

○ This tutorial contains affiliate links. Read our

disclosure policy

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 Tutorial

Cells containing empty text strings (zero-length strings) are counted. An example of an empty string, the result of an

IF function

, 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 Tutorial

Xem thêm: Bài tập có đáp án chi tiết về dạng 3 phương pháp tích phân từng phần mức độ 4

Examples of the COUNTBLANK Function

Let’s look at three COUNTBLANK examples.

image of an Excel spreadsheet containing 3 COUNTBLANK functions

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

IF function

(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.

Xem thêm: Cho ${z1},{z2}$ là hai số phức liên hợp của nhau đồng thời thỏa mãn $frac{{{z1}}}{{{z2}}} in mathbb{R}$ và $left

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.

image of an Excel spreadsheet containing COUNTBLANK functions and how a cell with a space is not counted by COUNTBLANK

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.

Xem thêm: Cách vẽ đồ thị hàm số bậc hai hàm số bậc 2

Final Thoughts

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

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Back to top button