HOW TO USE COUNTBLANK FUNCTION IN EXCEL?

Table of Contents

INTRODUCTION

COUNTBLANK FUNCTION comes under the STATISTICAL FUNCTIONS category in Excel.

It is an extension to the COUNT FUNCTION.

COUNTBLANK FUNCTION , as the name suggests, does the counting job in Excel , just like the COUNT FUNCTION with the difference that we can provide condition in COUNTBLANK FUNCTION which cells to count.

The other difference is that it works independently of the type of data which means that it would work for numbers as well as text or any other data type.

COUNTBLANK FUNCTION is very helpful when we need to use any nested functions to make a check on the number of cells containing the number or number type of data.

This function is also very helpful when we are programming VBA. We’ll be needing a lot of functions to know the status of the sheet so that we can decide what to do further. This function is one of those functions which are helpful.

Let us learn ,how to use this function.

PURPOSE OF COUNTBLANK FUNCTION IN EXCEL

COUNTBLANK FUNCTION returns the total number of blank cells from a given data set or range .

COUNTBLANK FUNCTION is apt for the situation when we want to count the number of cell which does’nt contain any value i.e. if they are blank or empty.

For example, suppose we have a large number of cells containing different words including some empty or blank cells. We can find out the number of total blank or empty cells within a second from the given row, column, table or any range by using COUNTBLANK function.

This function is a must to learn because we encounter such situations frequently while using excel.

PREREQUISITES TO LEARN COUNTBLANK FUNCTION

No specific prerequisite but to know how to use the function in Excel.


WHAT IS THE SYNTAX OF COUNTBLANK FUNCTION?

The Syntax [ the way the function is used ] for the function is

=COUNTBLANK(ARRAY OR RANGE OR TABLE)

ARRAY or RANGE or TABLE is the reference where you want to search for the blank cells.

EXAMPLE:COUNTBLANK FUNCTION IN EXCEL

Let us take an example to learn the use of COUNTBLANK function in Excel.

Let us take random numbers with a few blank cells.

SAMPLE DATA FOR THE EXAMPLE

The picture above shows a random data just for the demonstration of COUNTBLANK FUNCTION.



FOLLOW THE STEPS TO FIND OUT THE BLANK CELLS USING COUNTBLANK FUNCTION

  1. Select the cell where you want the result.
  2. Enter the formula as =COUNTBLANK(RANGE).
  3. For our example, we’ll use the formula as =COUNTBLANK(H3:AX23).
  4. Press ENTER.
  5. The result will appear.
  6. The following picture shows the result and usage.
RESULT

The result appears as 28. Try counting the blank cells.

So, in this way , we can find out the number of blank cells within seconds.

FAQs

THE CELL IS EMPTY EVEN THEN COUNTBLANK IS NOT COUNTING IT!

It happens if we have a space character ” ” in the cell but it won’t be visible.

Simply click the cell and press DELETE BUTTON on the keyboard.