The COUNTBLANK function with example in Excel

The COUNTBLANK function in Excel is used to count the number of empty cells within a specified range.

Syntax

excel
COUNTBLANK(range)

• range: The range of cells that you want to count the empty cells from.

Example

Suppose you have a list of values in cells A1 to A10, and you want to count how many of these cells are empty.

Data in Column A:

vbnet
A
1    10
2    15
3    
4    Text
5    
6    30
7    TRUE
8    
9    
10   45

1. Using the COUNTBLANK Function:
   • Formula: =COUNTBLANK(A1:A10)
   • Result: 3
   • Explanation: There are 3 empty cells in the range A1
     .

Step-by-Step Example

1. Input Data:

   • In cells A1 to A10, enter the following values:

     vbnet
     10
     15
     
     Text
     
     30
     TRUE
     
     
     45
     

2. Using COUNTBLANK Function:

   • In cell B1, enter the formula:

     excel
     =COUNTBLANK(A1:A10)
     

   • Press Enter.

3. Result:

   • The result in cell B1 will be 3, indicating that there are 3 empty cells in the range A1
     .

Example with Multiple Ranges

You can also use the COUNTBLANK function with multiple ranges.

Data:

css
A           B
1    5        30
2    10       25
3             
4    15       
5    20       35

1. Using the COUNTBLANK Function with Multiple Ranges:
   • Formula: =COUNTBLANK(A1:A5, B1:B5)
   • Result: 3
   • Explanation: There are 3 empty cells in the combined ranges A1
     and B1
     .

Step-by-Step Example with Multiple Ranges

1. Input Data:

   • In cells A1 to A5, enter:

     5
     10
     
     15
     20
     

   • In cells B1 to B5, enter:

     30
     25
     
     
     35
     

2. Using COUNTBLANK Function:

   • In cell C1, enter the formula:

     excel
     =COUNTBLANK(A1:A5, B1:B5)
     

   • Press Enter.

3. Result:

   • The result in cell C1 will be 3, indicating that there are 3 empty cells in the ranges A1
     and B1
     combined.

Conclusion

The COUNTBLANK function in Excel provides a simple way to count the number of empty cells within a specified range. It is particularly useful for data validation and analysis, allowing you to quickly identify and quantify missing or incomplete data.