Which function counts the number of blank cells within a range?

Home ➜ Excel Formulas ➜ 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.

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(A1:A10)

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

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.

And the moment, you hit enter it returns the 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.

Home ➜ Excel Formulas ➜ Count Total Number of Cells from a Range

In Excel, you can use two different formulas if you want to count the number of cells from a range (blank and non-blank cells). In this tutorial, we are going to look at both formulas.

Count Number of Cells (Method-1)

In the following, example we have a few values in column A, and you need to count the cells from the range A1:B10. And for this, we will use the ROWS and COLUMNS function.

Steps to write the formula:

1. First of all, in cell D1, enter “=ROWS (A1:B10)”.
2. Next, type the asterisk sign (*) to multiply the values.
3. After that, you need to enter the COLUMN function by referring to the same range (COLUMNS(A1:B10).
4. In the end, press enter button to the result in the cell.

Now as you can see, when you hit enter, it returns 20 in the result as you have a total of 20 cells in the range you have specified.

Now let’s try to understand the formula that we have used here. In the first part, we have used the ROWS function which returns the number of rows that you have in a range. Here it returns 10 as you have ten rows in the range A1 to B10.

Now when next you have the COLUMNS function that returns the number of columns you have in a range. Here it returns 2 as you have two columns in the range A1 to B10.

In the end, you have an asterisk sign between both the values to multiply rows and columns to get the total number of cells in the range.

Combine COUNTA – COUNTBLANK to Count Cells from a Range

The second method wants you to combine COUNTA and COUNTBLANK functions. By combining both functions you can count all the non-blank cells and all the blank cells from a range. Below we have the same example that we have used in the first example.

In the cell D1, you can enter the following formula:

=COUNTA(A1:B10)+COUNTBLANK(A1:B10)

As I said, this formula works in two parts:

1. COUNTA function counts the cells where you have values.
2. COUNTBLANK function counts the cells where you don’t have values.

In the end, you have a “+ sign” which sum both number and returns you the total count of cells that you have in the range that you have specified.