When auditing a massive dataset in Google Sheets, such as an attendance roster or a survey response form, finding out exactly how many people failed to submit an answer is critical. Manually scrolling through hundreds of rows to count empty, blank cells is tedious and highly prone to human error. Instead, you can command Google Sheets to instantly tally the exact number of empty cells within any given range using the COUNTBLANK() function.
How the COUNTBLANK Function Works
Unlike the standard COUNT() function (which only tallies cells containing numbers) or the COUNTA() function (which tallies cells containing text or numbers), the COUNTBLANK() function specifically targets cells that are completely empty.
The syntax for the formula is simple: =COUNTBLANK(range)
Step-by-Step Example
Suppose you sent a survey to 50 employees, and their responses are listed in Column C, from cell C2 down to C51. You want to know exactly how many employees ignored the question and left the cell completely blank.
- Click on an empty cell where you want the final tally to appear (for example, cell E2).
- Type the equal sign followed by the function name:
=COUNTBLANK( - Now, you must define the range you want Google Sheets to inspect. You can either use your mouse to click and drag over cells C2 through C51, or you can type the range manually.
- The formula should now look like this:
=COUNTBLANK(C2:C51) - Press the Enter key.
Google Sheets will instantly analyze those 50 cells and output a single number representing exactly how many cells are totally empty. If a user later goes in and adds a response to one of the previously blank cells, the COUNTBLANK() tally will automatically update in real-time.
Important Note: A cell must be entirely empty for this function to count it. If a cell contains a single hidden spacebar character (perhaps entered accidentally by a user), Google Sheets considers that cell to contain text, and it will not be counted as a blank cell.