Use COUNTBLANK to count empty or blank cells in a range in Excel
The COUNTBLANK function in Excel allows you to count the number of empty or blank cells in a range in an Excel spreadsheet. This lesson shows you how to use COUNTBLANK, and also shares a couple of things to watch out for when you use COUNTBLANK in an Excel formula.
The COUNTBLANK() syntax
COUNTBLANK has the following syntax:
=COUNTBLANK(range)
where range is the range of cells you want to count the empty cells for.
Note that COUNTBLANK returns the number of cells that are empty (i.e. cells with nothing in them) as well as any blank cells (i.e. cells containing a formula that is returning a blank or null value).
COUNTBLANK in action
The following table is an example of a situation where you might want to count the number of blank cells:
- The formula counts the blank cells in the range from B2:B8. As you can see, there are 3 blank cells in this range, which is the result returned by the formula.
- Note that COUNTBLANK doen't let you enter multiple ranges in the same way that other functions, such as the SUM function do. If you want to find the total number of blank cells in more than one range in an Excel spreadsheet, you would have to use the COUNTBLANK function twice in the same formula and add them together. Here's an example:
- =COUNTBLANK(B2:B8)+COUNTBLANK(D2:D8)
If you want to know the number of cells in a range that aren't blank, you could use the COUNT() or COUNTA() functions instead.
Join our mailing list
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.
As a part of my job recent days I do my works in excel, I have so much doubts in empty cells count. Internet is the best resource to solve our doubts about the functions. Thanks for sharing such informative page to solve the readers doubts. http://www.outlookproblemshelp.com