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.
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).
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 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.
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 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.
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.
To search for blank cells or cells containing text, type
=COUNTIF(range,"*") in a third empty cell. Replace range as required.
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.
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.
Post a Comment