Count empty cells in Excel
COUNTBLANK allows you to count the number of empty or blank cells in a range in Excel. 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:
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:
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.