Google Sheets

When you’re analyzing data in a spreadsheet, counting empty or blank cells could help you focus on specific areas. This is why functions like COUNTBLANK, COUNTIF, COUNTIFS, and SUMPRODUCT are so important in Google Sheets.

A word of warning, however. If you have a cell that contains an empty text string (“”) or has a formula that returns a similar result, this cell would be blank, but it wouldn’t technically be empty. If you want to know the number of truly empty cells, you’ll need to use a combination of the SUM, ROWS, COLUMNS, and COUNTIF functions.

Using COUNTBLANK

You can try the COUNTBLANK function to count the number of blank cells in a Google Sheets spreadsheet. This is the quickest way to find the number of blank, but not empty, cells.

Cells that contain numbers or text won’t be counted, including cells with the number zero. As we’ve mentioned, however, if a cell looks empty but contains an empty text string (“”), this will be counted.

An empty text string in a cell in Google Sheets

To use it, open your Google Sheets spreadsheet. Click on an empty cell and type =COUNTBLANK(range). Replace range with your cell range.

For instance, if you wanted to count the number of blank cells between columns A and C, you’d type =COUNTBLANK(A:C).

The COUNTBLANK function used to count blank cells in Google Sheets

In the example above, cells from A3 to H24 are used within the range. This range contains four blank cells (B4, C4, D4, and E4), which is the same figure COUNTBLANK reports in cell A1.

Using COUNTIF and COUNTIFS

While COUNTBLANK returns the number of blank cells, you can also use COUNTIF or COUNTIFS to achieve the same result.

COUNTIF counts the number of cells that meet the criteria you define within the formula itself. Because you want to count empty cells, you can use a blank text string as your criteria.

To use COUNTIF, open your Google Sheets spreadsheet and click on a blank cell. Type =COUNTIF(range,""), replacing range with your chosen cell range.

The COUNTIF function used to calculate blank cells in Google Sheets

The example above has three blank cells (B4, C4, and D4) within the range A3 to H24, with the COUNTIF function in cell A1 returning the same number of blank cells.

The COUNTIFS function can be used as an alternative to COUNTIF. Use =COUNTIFS(range,""), replacing range with your selected cell range.

The COUNTIFS function used in a Google Sheets spreadsheet

In the example above, four blank cells within the A3 to H24 cell range were found.

Using SUMPRODUCT

The SUMPRODUCT function offers a slightly more complex route to counting the number of blank cells. It counts the number of cells matching certain criteria which, in this case, would be an empty text string (“”).

To use SUMPRODUCT, open your Google Sheets spreadsheet and click on an empty cell. Type =SUMPRODUCT(--(range="")), replacing range with your chosen cell range.

The SUMPRODUCT function counting empty cells in Google Sheets

The example above shows that within the A2 to H24 cell range, two blank cells (B4 and C4) were found.

Counting Empty Cells

All of the functions listed above count cells that are blank but which aren’t technically empty. If a function returns a null or empty result, or if you have an empty text string (“”) in a cell, then those cells are counted as blank.

A workaround to this problem is to use COUNTIF to count the number of cells with a numerical value, then to use a second COUNTIF formula to count the number of cells containing text or empty text strings.

You can then add the results from these calculations and subtract them from the number of cells in your data range. You’ll need to know the number of cells in your range first. To find that out, you can use the ROWS and COLUMNS functions.

To start, open your Google Sheets spreadsheet, click on an empty cell and type =ROWS(range)*COLUMNS(range), replacing the range value with your cell range.

The ROWS and COLUMNS functions used to calculate the number of cells in a range in Google Sheets

In a second empty cell, type =COUNTIF(range,">=0") to count the number of cells with a numerical value. Once again, replace range with the appropriate cell range for your data.

The COUNTIF function in Google Sheets, being used to count the number of cells with a number value

To search for blank cells or cells containing text, type =COUNTIF(range,"*") in a third empty cell. Replace range as required.

The COUNTIF function, counting the number of cells with text/empty text strings in Google Sheets

You can then use SUM to add up your two COUNTIF values, subtracting that figure from the number of cells in your range calculated using the ROWS and COLUMNS functions.

In our example, the total number of cells can be found in cell B8, the number of cells with a numerical value in B9, and the number of cells that contain text or an empty text string in B10.

Replacing these cell values with your own, you could use =B8-SUM(B9:10) to determine the number of truly empty cells in your range.

The final calculation of empty cells in Google Sheets, using SUM

As the example above demonstrates, in a range of 20 cells (A2 to E5), 19 cells were found to have either a number, text, or empty text string. Only one cell, E4, was completely empty.

Ben Stockton Ben Stockton
Ben Stockton is a freelance tech writer from the United Kingdom. In a past life, he was a UK college lecturer, training teens and adults. Since leaving the classroom, he's been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. He has a degree in History and a postgraduate qualification in Computing.
Read Full Bio »