How one can Rely Clean or Empty Cells in Google Sheets

Google Sheets

If you’re analyzing knowledge in a spreadsheet, counting empty or clean cells might aid you give attention to particular areas. Because of this capabilities like COUNTBLANK, COUNTIF, COUNTIFS, and SUMPRODUCT are so essential in Google Sheets.

A phrase of warning, nevertheless. If in case you have a cell that accommodates an empty textual content string (“”) or has a formulation that returns an identical consequence, this cell could be clean, but it surely wouldn’t technically be empty. If you wish to know the variety of actually empty cells, you’ll want to make use of a mix of the SUM, ROWS, COLUMNS, and COUNTIF capabilities.

Utilizing COUNTBLANK

You’ll be able to attempt the COUNTBLANK operate to rely the variety of clean cells in a Google Sheets spreadsheet. That is the quickest method to discover the variety of clean, however not empty, cells.

Cells that comprise numbers or textual content gained’t be counted, together with cells with the quantity zero. As we’ve talked about, nevertheless, if a cell appears empty however accommodates an empty textual content string (“”), this will likely be counted.

An empty text string in a cell in Google Sheets

To make use of it, open your Google Sheets spreadsheet. Click on on an empty cell and kind =COUNTBLANK(vary). Change vary along with your cell vary.

For example, in the event you wished to rely the variety of clean cells between columns A and C, you’d sort =COUNTBLANK(A:C).

The COUNTBLANK function used to count blank cells in Google Sheets

Within the instance above, cells from A3 to H24 are used throughout the vary. This vary accommodates 4 clean cells (B4, C4, D4, and E4), which is identical determine COUNTBLANK studies in cell A1.

Utilizing COUNTIF and COUNTIFS

Whereas COUNTBLANK returns the variety of clean cells, you can too use COUNTIF or COUNTIFS to realize the identical consequence.

COUNTIF counts the variety of cells that meet the standards you outline throughout the formulation itself. Since you need to rely empty cells, you need to use a clean textual content string as your standards.

To make use of COUNTIF, open your Google Sheets spreadsheet and click on on a clean cell. Sort =COUNTIF(vary,""), changing vary along with your chosen cell vary.

The COUNTIF function used to calculate blank cells in Google Sheets

The instance above has three clean cells (B4, C4, and D4) throughout the vary A3 to H24, with the COUNTIF operate in cell A1 returning the identical variety of clean cells.

The COUNTIFS operate can be utilized as an alternative choice to COUNTIF. Use =COUNTIFS(vary,""), changing vary along with your chosen cell vary.

The COUNTIFS function used in a Google Sheets spreadsheet

Within the instance above, 4 clean cells throughout the A3 to H24 cell vary have been discovered.

Utilizing SUMPRODUCT

The SUMPRODUCT operate affords a barely extra advanced path to counting the variety of clean cells. It counts the variety of cells matching sure standards which, on this case, could be an empty textual content string (“”).

To make use of SUMPRODUCT, open your Google Sheets spreadsheet and click on on an empty cell. Sort =SUMPRODUCT(--(vary="")), changing vary along with your chosen cell vary.

The SUMPRODUCT function counting empty cells in Google Sheets

The instance above reveals that throughout the A2 to H24 cell vary, two clean cells (B4 and C4) have been discovered.

Counting Empty Cells

All the capabilities listed above rely cells which are clean however which aren’t technically empty. If a operate returns a null or empty consequence, or when you have an empty textual content string (“”) in a cell, then these cells are counted as clean.

A workaround to this drawback is to make use of COUNTIF to rely the variety of cells with a numerical worth, then to make use of a second COUNTIF formulation to rely the variety of cells containing textual content or empty textual content strings.

You’ll be able to then add the outcomes from these calculations and subtract them from the variety of cells in your knowledge vary. You’ll have to know the variety of cells in your vary first. To seek out that out, you need to use the ROWS and COLUMNS capabilities.

To begin, open your Google Sheets spreadsheet, click on on an empty cell and kind =ROWS(vary)*COLUMNS(vary), changing the vary worth along with your cell vary.

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

In a second empty cell, sort =COUNTIF(vary,">=0") to rely the variety of cells with a numerical worth. As soon as once more, change vary with the suitable cell vary on your knowledge.

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

To seek for clean cells or cells containing textual content, sort =COUNTIF(vary,"*") in a 3rd empty cell. Change vary as required.

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

You’ll be able to then use SUM so as to add up your two COUNTIF values, subtracting that determine from the variety of cells in your vary calculated utilizing the ROWS and COLUMNS capabilities.

In our instance, the entire variety of cells will be present in cell B8, the variety of cells with a numerical worth in B9, and the variety of cells that comprise textual content or an empty textual content string in B10.

Changing these cell values with your personal, you could possibly use =B8-SUM(B9:10) to find out the variety of actually empty cells in your vary.

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

As the instance above demonstrates, in a variety of 20 cells (A2 to E5), 19 cells have been discovered to have both a quantity, textual content, or empty textual content string. Just one cell, E4, was utterly empty.

Leave a Reply

Your email address will not be published. Required fields are marked *