Count Blank (Empty) Cells using COUNTIF

If you want to count cells that are blank in Excel, you can use the COUNTBLANK function which is specifically designed to count cells that are empty (without any value in them). In the COUNTBLANK function, you just need to refer to the range from where you want to count the blank cells.

Use COUNTBLANK Function

In the following example, you have a few values in the range A1:A10, but a few of the cells are empty. And now, you need to count the cells those cells with no values in them.

range-with-blank-cells

You can use the following steps to write this formula:

  1. First, in cell B1, start typing the COUNTBLANK function (=COUNTBLANK).
  2. After that, Type the starting parentheses.
  3. Now, refer to the range A1:A10 in the function.
  4. In the end, type a closing parenthesis and hit enter.
countblank-formula

=COUNTBLANK(A1:A10)

Once you hit enter it returns the count of the cells that are blank in the specified range.

countblank-resulted-value

Using COUNTIF to Count Blank Cells

You can also use COUNTIF and create a condition to count blank cells. By using the same example, you can follow the below steps to write this formula:

  1. First, in cell B1, start typing the COUNTIF function (=COUNTIF), and enter starting parenthesis.
  2. Now, refer to the range A1:A10 from where you want to count the cells with no value.
  3. Next, in the criteria argument, type “=”. This equals operator tells Excel to count cells where you have no value because with = operator you have not specified anything.
  4. After that, type the closing parentheses and hit enter.
countif-to-count-blank-cells

And the moment, you hit enter it returns the count for blank cells.

count-for-blank-cells

You can also use a formula like the following with the “=”&”” criteria. When you use it, it also tells Excel to count only cells with no value in them.

countif-criteria-for-blank-cells