Counting Blank or Empty Cells in Excel

Excel COUNTBLANK Function

Excel's COUNTBLANK function
Count Blank or Empty Cells with Excel's COUNTBLANK Function. (Ted French)

Excel has several Count Functions that can be used to count up the number of cells in a selected range that contain a specific type of data.

The job of the COUNTBLANK function is to count up the number of cells in a selected range that are:

  • Empty - cells containing no data
  • Blank - cells containing a formula that returns a blank or null value

Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the COUNTBLANK function is:

=COUNTBLANK( Range)

The range (required) is the group of cells the function is to search.

Notes:

  • The Range argument must be a contiguous group of cells.
  • Since COUNTBLANK does not permit multiple ranges to be entered for the Range argument, multiple instances of the function can be entered in a single formula to find the number of blank or empty cells in two or more non-contiguous ranges, as shown in row 3 of the example above: 

    =COUNTBLANK(A2:A10) + COUNTBLANK(B2:B10)

Example

In the image above, several formulas containing the COUNTBLANK function are used to count the number of blank or empty cells in two ranges of data: A2 to A10 and B2 to B10.

Entering the COUNTBLANK Function

Options for entering the function and its arguments include:

  1. Typing the complete function shown above into a worksheet cell;
  2. Selecting the function and its arguments using the COUNTBLANK function dialog box

    Although it is possible to just type the complete function in manually, many people find it easier to use the dialog box which looks after entering the correct syntax for the function.

    Note: Formulas containing multiple instances of COUNTBLANK, such as those seen in rows three and four of the image, cannot be entered using the function's dialog box, but must be entered manually.

    The steps below cover entering the COUNTBLANK function shown in cell D2 in the image above using the function's dialog box.

    To Open the COUNTBLANK Function Dialog Box

    1. Click on cell D2 to make it the active cell - this is where the function's results will be displayed;
    2. Click on the Formulas tab of the ribbon;
    3. Click on More Functions > Statistical to open the function drop down list;
    4. Click on COUNTBLANK in the list to bring up the function's dialog box;
    5. Click on the Range line in the dialog box;
    6. Highlight cells A2 to A10 in the worksheet to enter these references as the Range argument;
    7. Click OK to complete the function and return to the worksheet;
    8. The answer "3" appears in cell C3 because there are three blank cells ( A5, A7, and A9 ) in the range A to A10.
    9. When you click on cell E1 the complete function =COUNTBLANK ( A2 : A10 ) appears in the formula bar above the worksheet.

    COUNTBLANK Alternative Formulas

    Alternatives to COUNTBLANK that can be used include those shown in rows five to seven in the image above.

    For example, the formula in row five, =COUNTIF(A2:A10,""), uses the COUNTIF function to find the number of blank or empty cells in the range A2 to A10 and gives the same results as COUNTBLANK.

    The formulas in rows six and seven, on the other hand,  find blank or empty cells in multiple ranges and only count those cells that meet both conditions. These formulas offer more flexibility in what blank or empty cells in a range gets counted.

    For example, the formula in row six, =COUNTIFS(A2:A10,"",B2:B10,""), uses the COUNTIFS to find blank or empty cells in multiple ranges and only counts those cells that have blank cells in the same row of both ranges—row seven.

    The formula in row seven, =SUMPRODUCT((A2:A10="bananas")*(B2:B10="")), uses the SUMPRODUCT function to count only those cells in multiple ranges that meet both conditions—that of containing bananas in the first range (A2 to A10) and being blank or empty in the second range (B2 to B10).

    Format
    mla apa chicago
    Your Citation
    French, Ted. "Counting Blank or Empty Cells in Excel." ThoughtCo, Jul. 24, 2017, thoughtco.com/count-blank-or-empty-cells-3123407. French, Ted. (2017, July 24). Counting Blank or Empty Cells in Excel. Retrieved from https://www.thoughtco.com/count-blank-or-empty-cells-3123407 French, Ted. "Counting Blank or Empty Cells in Excel." ThoughtCo. https://www.thoughtco.com/count-blank-or-empty-cells-3123407 (accessed January 19, 2018).