How to use Countif to count the blank rows in Excel

In this tutorial we will learn how we can use Excel’s built-in “COUNTIF” function to calculate the number of empty cells. The best way to understand how countif works is to first understand the structure of the function and the parameters it requires.

Structure of COUNTIF

The standard syntax of countif function in excel is shown below;

=COUNTIF(range, criteria)

• range (where to look in for something)

A range can be a single column with multiple rows or it could consist of multiple columns and multiple rows. For example “A1:B10”. This will tell the formula to search through two columns A & B till the 10th row.

• criteria (what to look for)

Criteria will be a special logical test which will depend upon the situation and the nature of values that we are looking for. In our example we are looking for a blank cell so we will use a special logical condition in the formula which will tell Excel to count only those cells which are blank.

Implementing the Formula on an Actual Data Set

Let’s take an example data set and find out the number of empty or blank cells in it. So let’s consider the data set shown above.

We can see in the example data set that a blank entry in the “Paid at” column shows that the task fulfilment status is pending. So it means if I just want to know how many tasks are pending I can achieve that only by counting the blank cells in that particular column.

Let’s see how we can use Excel’s built-in formulas to find a solution to our problem by following these step;

When we are working with data sets to analyze them in Excel, often there is a requirement to count those cells which are empty. Excel provides some very easy ways to find out the number of blank cells within a specific range.

Step 1 – Implement the formula in a suitable cell

– Choose a suitable cell where you wish to implement the formula.

– Use the following formula in that cell and press enter key.

= COUNTIF(D2:D14,””)

– The desired result will be displayed in this cell as soon as you will press the enter key as shown in the picture above.

So by using COUNTIF with a special condition to count the blank rows in column D we can find out that only 4 tasks are pending in the tasks lists.

Breakdown of the Logical Condition used inside the formula

We used the formula COUNTIF(D2:D14,””). Let’s see how we told Excel where to look and what to look for. 

• The first parameter D2:D14 tells Excel that the data range in which we are going to look for something is in column D and from row 2 to row 14 only.
• The second parameter was “”. This “” is a special character which represents a “BLANK”. So when we use this in the condition parameter it tells Excel to look for a cell that is totally blank. When Excel finds a blank cell with nothing in it. The counter is increased. Our data set had four blank cells in that range so the result was 4 as we desired.