*The tutorial discusses the syntax and basic uses of the COUNTBLANK function to count the number of blank cells in Excel.*

In a couple of recent posts, we've discussed different ways to identify blank cells and highlight blanks in Excel. In some situations, however, you may want to know how many cells do not have anything in them. Microsoft Excel has a special function for this too. This tutorial will show you the fastest and most convenient methods to get the number of empty cells in a range as well as totally blank rows.

The COUNTBLANK function in Excel is designed to count empty cells in a specified range. It belongs to the category of Statistical functions and is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.

The syntax of this function is very straightforward and requires just one argument:

COUNTBLANK(range)

Where *range* is the range of cells in which blanks are to be counted.

Here is an example of the COUNTBLANK formula in Excel in its simplest form:

`=COUNTBLANK(A2:D2)`

The formula, entered in E2 and copied down to E7, determines the number of empty cells in columns A through D in each row and returns these results:

To effectively use an Excel formula for counting blank cells, it is important to understand what cells the COUNTBLANK function considers as "blanks".

- Cells that contain any text, numbers, dates, logical values or errors are not counted.
- Cells containing
**zeros**are considered non-blank and are not counted. - Cells containing formulas that return
**empty strings**("") are considered blank and are counted.

Looking at the screenshot above, please notice that cell A7 containing a formula that returns an empty string is counted twice:

- COUNTBLANK considers a zero-length string as an empty cell because it appears blank.
- COUNTA treats a zero-length string as a non-empty cell because it actually contains a formula.

That may sound a bit illogical, but Excel does work this way :)

COUNTBLANK is the most convenient but not the only way to count empty cells in Excel. The following examples demonstrate a few other methods and explain which formula is best to be used in which scenario.

Whenever you need to count blanks in Excel, COUNTBLANK is the first function to try.

For example, to get the number of empty cells in each row in the table below, we enter the following formula in F2:

`=COUNTBLANK(A2:E2)`

As we use relative references for the range, we can simply drag the formula down and the references will adjust automatically for each row, producing the following result:

Another way to count empty cells in Excel is to use the COUNTIF or COUNTIFS function or with an empty string ("") as the criteria.

In our case, the formulas would go as follows:

`=COUNTIF(B2:E2, "")`

Or

`=COUNTIFS(B2:E2, "")`

As you can see in the screenshot below, the results of COUNTIFS are exactly the same as those of COUNTBLANK, so which formula to use in this scenario is a matter of your personal preference.

In a situation, when you want to count empty cells based on some condition, COUNTIFS is the right function to use as its syntax provides for **multiple criteria**.

For instance, to determine the number of cells that have "Apples" in column A and blanks in column C, use this formula:

`=COUNTIFS(A2:A9, "apples", C2:C9, "")`

Or input the condition in a predefined cell, say F1, and refer to that cell as the criteria:

`=COUNTIFS(A2:A9, F1, C2:C9, "")`

In some cases, you may need not just count blank cells in a range, but take some action depending on whether there are any empty cells or not.

Although there is no built-in IF COUNTBLANK function in Excel, you can easily make your own formula by using the IF and COUNTBLANK functions together. Here's how:

- Check if the blanks count equals zero and put this expression in the logical test of IF:

`COUNTBLANK(B2:D2)=0`

- If the logical test evaluates to TRUE, output "No blanks".
- If the logical test evaluates to FALSE, output "Blanks".

The complete formula takes this shape:

`=IF(COUNTBLANK(B2:D2)=0, "No blanks", "Blanks")`

As the result, the formula identifies all the rows where one or more values are missing:

Or you can run another function depending on the blanks count. For instance, if there are no empty cells in the range B2:D2 (i.e. if COUNTBLANK returns 0), then sum the values, otherwise return "Blanks":

`=IF(COUNTBLANK(B2:D2)=0, SUM(B2:D2), "Blanks")`

Supposing you have a table in which some rows contain information while other rows are totally blank. The question is - how do you get the number of rows that do not contain anything in them?

The easiest solution that comes to mind is to add a helper column and fill it with the Excel COUNTBLANK formula that finds the number of blank cells in each row:

`=COUNTBLANK(A2:E2)`

And then, use the COUNTIF function to find out in how many rows all the cells are blank. Since our source table contains 5 columns (A through E), we count the rows that have 5 empty cells:

`=COUNTIF(F2:F8, 5))`

Instead of "hardcoding" the number of columns, you can use the COLUMNS function to calculate it automatically:

`=COUNTIF(F2:F8, COLUMNS(A2:E2))`

If you do not want to mangle the structure of your beautifully designed worksheet, you can achieve the same result with a lot more complex formula that does not however require any helper columns nor even array entering:

`=SUM(--(MMULT(--(A2:E8<>""), ROW(INDIRECT("A1:A"&COLUMNS(A2:E8))))=0))`

Working from the inside out, here's what the formula does:

- First, you check the entire range for non-blank cells by using the expression like A2:E8<>"", and then coerce the returned logical values of TRUE and FALSE to 1's and 0's by using the double unary operator (--). The result of this operation is a two-dimensional array of ones (non-blanks) and zeros (blanks).
- The purpose of the ROW part is to generate a vertical array of numeric
**non-zero**values, in which the number of elements equals to the number of columns of the range. In our case, the range consists of 5 columns (A2:E8), so we get this array: {1;2;3;4;5} - The MMULT function calculates the matrix product of above arrays and produces a result like: {11;0;15;8;0;8;10}. In this array, the only thing that matters for us is 0 values that represent the rows where all cells are blank.
- Finally, you compare each element of the above array against zero, coerce TRUE and FALSE to 1 and 0, and then sum the elements of this final array: {0;1;0;0;1;0;0}. Keeping in mind that 1's correspond to blank rows, you get the desired result.

If the above formula seems too difficult for you to comprehend, you may like this one better:

`=SUM(--(COUNTIF(INDIRECT("A"&ROW(A2:A8) & ":E"&ROW(A2:A8)), "<>"&"")=0))`

Here, you use the COUNTIF function to find how many non-blank cells there are in each row, and INDIRECT "feeds" the rows to COUNTIF one by one. The result of this operation is an array like {4;0;5;3;0;3;4}. A check for 0, transforms the above array to {0;1;0;0;1;0;0} where 1's represent blank rows, so you just need to add them up.

In all the previous examples, we were counting blank cells including those that only appear blank but, in reality, contain empty strings ("") returned by some formulas. In case you'd like to exclude zero-length strings from the result, you can use this generic formula:

ROWS(*range*) * COLUMNS(*range*) - COUNTA(*range*)

What the formula does is to multiply the number of rows by the number of columns to get the total of cells in the range, from which you subtract the number of non-blanks returned by COUNTA. As you may remember, the Excel COUNTA function considers empty strings as non-blank cells, so they won't be included in the final result.

For example, to determine how many absolutely empty cells there are in the range A2:A8, here's the formula to use:

`=ROWS(A2:A8) * COLUMNS(A2:A8) - COUNTA(A2:A8)`

The screenshot below shows the result:

That's how to count empty cells in Excel. I thank you for reading and hope to see you on our blog next week!

Count blank cells formula examples

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 11 responses to "Excel COUNTBLANK and other functions to count empty cells"

To count the number of blanks in each row for a particular column(E2) in all the sheets('17-Mar' till '12-Jun') in workbook in excel.

Hello!

The COUNTBLANK function cannot count the number of empty cells using a 3D reference. Therefore, it must be used in the usual way, as described in this tutorial above.

How can I count blank cells in a specific column, with a row count that increases without having to change the Row range in the formula? I have 100 rows now and know how many blank cells are in the column. But now five more rows are added and the rows now go to 105. How to account for the increase without changing the formula?

I am using this formula now, but it counts ALL the rows and I need it to stop at 100, but account for rows inserted at the top. The COUNTIFS formula is suppose to be MTD check condition too, not sure if it is working either.

=COUNTBLANK(Cases!$E:$E) + COUNTIFS(Cases!C2, ">="&EOMONTH(TODAY(),-1)+1, Cases!C2, "<"&EOMONTH(TODAY(),0)+1)

Thanks,

Ron G

Hello!

I am not sure I fully understand what you mean. Your formula works with the entire column E. What does the “it counts ALL the rows and I need it to stop at 100” phrase mean? What do you want to calculate exactly? Your question is not entirely clear, please specify.

How to count blank cells in column B, but only for unique names in column A.

Hello!

The formula below will do the trick for you:

=SUM(COUNTIFS(B2:B10,"",A2:A10,UNIQUE(A2:A10,FALSE,TRUE)))

You can learn more about UNIQUE function in Excel in this article on our blog.

To conditionally count blank cells, use the COUNTIFS function.

That formula doesn't seem to be working for me. In column B I have 19 blanks and in column A I have 18 unique names, but the formula is generating 17. Could I be doing something wrong?

Thanks.

Hello!

The formula I sent to you was created based on the description you provided in your first request. You have not specified which unique values you need. Formula extract values that occur only once. You may be looking for distinct values that occur more than once. Read carefully how the UNIQUE function works. Perhaps the function arguments need to be changed.

=SUM(COUNTIFS(B2:B8,"",A2:A8,UNIQUE(A2:A8,FALSE,FALSE)))

If this is not what you wanted, please describe the problem in more detail. Give an example of the source data and the expected result.

Sorry, I thought I was making it easier the first time :) ...this is my problem.

In column A I have a list of names (that may be listed more than once), in column B I have volunteer hours associated with each name. I would like to generate a count of inactive volunteers by the hour cells that are blank (no hours=inactive) in column B but if it's a duplicate name in column A to only count the blank once.

I put the first formula in for this month (which all cells in column B are blank), which should return 18 but it returned 17.

Thank you!

Hi!

I think the second formula will work for you. I already wrote it:

=SUM(COUNTIFS(B2:B8,””,A2:A8,UNIQUE(A2:A8,FALSE,FALSE)))

Hi!

That one returns 19,so it counted the blank for the duplicate name both times.