HOW TO COUNT BLANK OR EMPTY CELLS IN EXCEL?

Table of Contents

INTRODUCTION

Counting specific data is a very frequent requirement in Excel in our day to day work.

Count can be required many times while preparing any sheet for a particular purpose.

In this article , let us try to count the number of blank cells in any column or row.



PURPOSE

The count of the cells which are blank can be of utmost requirement when we need to search for the cells which are not filled or kept blank intentionally.

The meaning of the blank cell can be specific as per your requirement .

For example,

  1. If we want to check if all the fields have been filled by the user or not. [ Data Validation can also be used for the same but we won’t be able to know the exact number of unfilled data fields.
  2. If we want to count the empty cells to find out the value not found cells.
  3. If we want to process the blank cells with any data.

or there can be more occassion as per your requirement.


EXAMPLE 1: COUNT THE BLANK CELLS IN A COLUMN OF EXCEL

Let us take random data to try out our examples.

DATA
HELLO
HI
WHAT
ARE
YOU
DOING
I
AM
HAPPY
EXAMPLE DATA


Let us discuss the example in various ways.

FOLLOW THE STEPS TO COUNT THE BLANK CELLS IN A COLUMN

WAY 1:

Simply use the function COUNTBLANK.

If this function is not present, follow Way 2.

WAY 2:

  1. Select the cell where you want the result.
  2. Enter the formula as =SUM(IF(COUNTIF(RANGE,RANGE)=0,1,0)).
  3. For our example, use the formula as =SUM(IF(COUNTIF(H7:H18,H7:H18)=0,1,0)).
  4. The count of the blank cells will appear as the result.
  5. The following picture shows the result.

COUNTING THE BLANK CELLS IN EXCEL

EXPLANATION:

We have made use of the function SUM, IF and COUNTIF functions to search the blank or empty cells in Excel.

The used formula is =SUM(IF(COUNTIF(H7:H18,H7:H18)=0,1,0)).

where H7:H18 contains the Range of the column is the range containing the data.

The innermost function is COUNTIF which will search the range and compare the output with the 0 . It is obvious that the empty cells will return an output of 0. We’ve nested this function inside IF and IF function is converting all the counts with 0 to 1.

Finally SUM function is totaling all the 1s and we get our result.



EXAMPLE 2: COUNT THE BLANK CELLS IN A ROW OF EXCEL

Let us take random data to try out our examples.

DATA


Let us discuss the example in various ways.

FOLLOW THE STEPS TO COUNT THE BLANK CELLS IN A ROW

Simply use the function COUNTBLANK.

If this function is not present, follow Way 2.

WAY 2:

  1. Select the cell where you want the result.
  2. Enter the formula as =SUM(IF(COUNTIF(RANGE,RANGE)=0,1,0)).
  3. For our example, use the formula as =SUM(IF(COUNTIF(D4:O4,D4:O4)=0,1,0)).
  4. The count of the blank cells will appear as the result.
  5. The following picture shows the result.

COUNTING THE BLANK CELLS IN EXCEL

EXPLANATION:

We have made use of the function SUM, IF and COUNTIF functions to search the blank or empty cells in Excel.

The used formula is =SUM(IF(COUNTIF(D4:O4,D4:O4)=0,1,0)).

where D4:O4 contains the Range of the column is the range containing the data.

The innermost function is COUNTIF which will search the range and compare the output with the 0 . It is obvious that the empty cells will return an output of 0. We’ve nested this function inside IF and IF function is converting all the counts with 0 to 1.

Finally SUM function is totaling all the 1s and we get our result.



FAQs

BLANK CELL IS PRESENT IN THE DATA BUT THE COUNT DOESN’T SHOW IT

The solution shown above needs the cell to be empty. If you have mistakenly using a SPACE, it won’t show it as empty.

To remove this issue, select the blank cell and click DELETE key on the keboard.

The issue should be remove.