Count Blank or Empty Cells in Google Spreadsheets

01
of 01

Google Spreadsheets COUNTBLANK Function

Count Blank or Empty Cells with Google Spreadsheets COUNTBLANK Function
Count Blank or Empty Cells with Google Spreadsheets COUNTBLANK Function. © Ted French

COUNTBLANK Function Overview

Google Spreadsheets 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 - cell A5 in the example in the image above.

The COUNTBLANK Function's 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 )

range - (required) one or more cells with or without data that are to be included in the count.

The range argument can contain:

Notes:

  • The range argument must be a contiguous group of cells;
  • No limit is listed in Google Spreadsheets help file as to the number of cells that can be included in the range being counted;
  • 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 - row 3 above: 

Example: Count Blank Cells with the COUNTBLANK Function

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.

The steps listed below will enter the COUNTBLANK function located in cell C2 in the image above.

Entering the COUNTBLANK Function

Google Spreadsheets does not use dialog boxes to enter a function's arguments as can be found in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

  1. Click on cell C2 to make it the active cell;
  2. Type the equal sign ( = ) followed by the name of the function countblank;
  3. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter C;
  4. When the name COUNTBLANK appears in the box, press the Enter key on the keyboard to enter the function name and open parenthesis (round bracket) into cell C5;
  5. Highlight cells A2 to A10 to include them as the function's range argument;
  6. Press the Enter key on the keyboard to add the closing parenthesis and to complete the function;
  7. The answer 3 should appear in cell C2 since three cells in the range contains no data - A5, A7, and A9;
  8. When you click on cell A8 the completed formula =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 formula in row six finds blank or empty cells in multiple ranges using the COUNTIFS function and only count those cells that meet both conditions (there are blank cells in the same row of both ranges - A2:A10 and B2:B10).

These formulas offer more flexibility in what blank or empty cells in a range gets counted.

Format
mla apa chicago
Your Citation
French, Ted. "Count Blank or Empty Cells in Google Spreadsheets." ThoughtCo, Nov. 6, 2016, thoughtco.com/google-spreadsheets-countblank-function-3123880. French, Ted. (2016, November 6). Count Blank or Empty Cells in Google Spreadsheets. Retrieved from https://www.thoughtco.com/google-spreadsheets-countblank-function-3123880 French, Ted. "Count Blank or Empty Cells in Google Spreadsheets." ThoughtCo. https://www.thoughtco.com/google-spreadsheets-countblank-function-3123880 (accessed January 17, 2018).