When working on Google Sheets, sometimes you need to count the number of blank cells in a column, row, or matrix. This is most often the case when dealing with external data imported into the Google Sheets. To count these blank cells, there are multiple methods. Each one is useful in a different scenario.
However, we will go over each of these to know how to count blank cells whenever you need to do so. There are some built-in functions inside Google Sheets that can be used in these cases. Knowing how to apply them for counting blank cells can be pretty helpful. Here, you will find some other Google Sheets tricks that might prove beneficial.
Blank and empty cells in Google Sheets
Many people consider blank cells and empty cells as the same thing. However, this is not the case. These are two different types of cells treated differently by Google Sheets. A blank cell is a cell that does not have any visible content in it. Therefore, a cell that contains a text string of spaces will be blank. However, this cell is not empty because it has a text string.
On the other hand, an empty cell is the one that is well and truly empty. Therefore, we can say that all empty cells are blank, but all blank cells are not empty. Now that you have cleared the confusion between the two, you should know that there are different functions for counting blank and empty cells.
Google Sheets functions useful for this include COUNTIF, COUNTIFS, COUNTBLANK, SUMPRODUCT, SUM, COLUMNS, and ROWS. Before applying one of these, you should know whether you want to see the number of blank or empty cells on your sheet. We will show you how to use each function to count empty and blank cells. Also, once you know the total number of blank or empty cells in a sheet or a specific part of the sheet, you can subtract that number from the total number of cells to find out the number of filled cells. That is why there is no dedicated function for that purpose inside Google Sheets. If you want to try your hand at some alternatives, here is a list of the top 5. Without further ado, let us dive into these methods one by one.
COUNTIF Or COUNTIFS function
Firstly, we will see the usage of COUNTIF and COUNTIFS functions. The COUNTIF function counts the number of cells with the same content as the one defined in its parameters. This way, if you use COUNTIF with an empty text string, it will count the number of all blank cells. For counting blank cells with COUNTIF function, you will have to follow these steps,
Step 1: Open the desired sheet on Google Sheets.
Google Sheets Opened
Step 2: Click on a blank cell inside the sheet.
Selecting A Blank Cell
Step 3: Type “=COUNTIF(A5:G10,””)” in the “Formula Bar”. Press the Enter key. Make sure to replace A5:G10 with the range of cells you are interested in.
Using COUNTIF Function
Step 4: After you press the Enter key, the cell you selected will display the number of blank cells in that range.
Number Of Blank Cells With COUNTIF
Function COUNTIFS can also be used with the same syntax and method. The only difference between these functions is that COUNTIF can only check for one condition, whereas COUNTIFS can check for multiple conditions in the same range. Since only one condition is required to check if a cell is blank or empty, you can use both COUNTIF and COUNTIFS with no difference in the results.
SUMPRODUCT Function
Using the SUMPRODUCT function for counting the number of blank or empty cells is a little more complex than COUNTIF or COUNTIFS. However, the main working principle behind the search is the same. The SUMPRODUCT function also counts the number of cells with the same content as defined in its parameters. This way, if you use SUMPRODUCT with an empty text string, it will count the number of all blank cells. As you would have already guessed, this syntax is a bit different. For counting blank cells with SUMPRODUCT function, you will have to observe the following steps,
Step 1: Open the desired sheet on Google Sheets.
Google Sheets Opened
Step 2: Click on a blank cell inside the sheet.
Selecting A Blank Cell
Step 3: Type “=SUMPRODUCT(–(A5:G10=””))” in the “Formula Bar”. Press the Enter key. Make sure to replace A5:G10 with the range of cells you are interested in.
Using SUMPRODUCT Function
Step 4: After you press the Enter key, the cell you selected will display the number of blank cells in that range.
Number Of Blank Cells With SUMPRODUCT
Here, you should note that the actual usage of the SUMPRODUCT function is not to count blank or empty cells. We have modified its use case to be applied to our advantage. Usually, this function is used to find out the sum of products of cells in the range. In the case of blank or empty cells, the individual values for the sum are all 1. Therefore, the sum of products equals the total number of blank cells.
COUNTBLANK function
Another function most commonly used for counting the number of blank cells is COUNTBLANK. The only parameter for this function is the range of cells you need to be looked over. This function is straightforward and provides the most straightforward way of counting blank cells. However, it is only available if you have the latest version of Google Sheets available to you. For counting blank cells with COUNTBLANK function, you will have to take the steps mentioned below,
Step 1: Open the desired sheet on Google Sheets.
Google Sheets Opened
Step 2: Click on a blank cell inside the sheet.
Selecting A Blank Cell
Step 3: Type “=COUNTBLANK(A5:G10)” in the “Formula Bar”. Press the Enter key. Make sure to replace A5:G10 with the range of cells you are interested in.
Using COUNTBLANK Function
Step 4: After you press the Enter key, the cell you selected will display the number of blank cells in that range.
Number Of Blank Cells With COUNTBLANK
Method for counting empty cells
By using COUNTIF, COUNTIFS, SUMPRODUCT, and COUNTBLANK functions, the result you get is the number of all blank cells. So, if any cells have an empty text string in them or display the result of a function that does not return anything, then those cells will be counted as well. Let us suppose that you want to count the number of empty cells. In that case, you will have to find the number of cells with numerical values inside them and the number of cells with text inside them. After that, you add these two numbers and subtract the answer from the total number of cells in that range. This will give you the number of empty cells. All steps for this method are listed below,
Step 1: Open the desired sheet on Google Sheets.
Google Sheets Opened
Step 2: Click on a blank cell inside the sheet.
Selecting A Blank Cell
Step 3: Type “=ROWS(A5:G10)*COLUMNS(A5:G10)” in the “Formula Bar”. Press the Enter key. Make sure to replace A5:G10 with the range of cells you are interested in.
Using ROWS And COLUMNS Functions
Step 4: After you press the Enter key, the cell you selected will display the number of all cells in that range.
Number Of All Cells
Step 5: Click on another blank cell inside the sheet.
Selecting Second Blank Cell
Step 6: Type “=COUNTIF(A5:G10,”>=0”)” in the “Formula Bar”. Press the Enter key. Make sure to replace A5:G10 with the range of cells you are interested in.
Using COUNTIF Function For Numerical Cells
Step 7: After you press the Enter key, the second cell you selected will display the number of cells that have numerical values inside them.
Number Of Cells With Numericals
Step 8: Click on another blank cell inside the sheet.
Selecting Third Blank Cell
Step 9: Type “=COUNTIF(A5:G10,”*”)” in the “Formula Bar”. Press the Enter key. Make sure to replace A5:G10 with the range of cells you are interested in.
Using COUNTIF Function For Text Cells
Step 10: After you press the Enter key, the third cell you selected will display the number of cells that have text inside them.
Number Of Cells With Text
Once all steps are completed, you can subtract the sum of second and third cells from the total number of cells. This will give you the number of empty cells in that range.
Number Of Empty Cells
This is quite an extensive method, but it is the only one to count truly empty cells. If you need to do this more often, you can write a macro for it inside Google Sheets. This way, you will not have to go through these steps again and again.
Conclusion
When a data set is imported in Google Sheets, it is usually analyzed and evaluated. In most cases, knowing the cells that show up empty or blank in the data set can tell you more about what it represents. Follow this tutorial on opening and editing office files on Google Sheets in Chromebook. On the other hand, sometimes, you may want to check if a large spreadsheet has all cells filled with data. In these circumstances, counting the number of blank or empty cells with the help of Google Sheets functions can be helpful. Once you know the methods, you will find their applications rather frequently.
