If you would like to count blank or empty cells in Excel and Google Sheets, listed below are the precise formulation you want to use. There are 3 ways to count blank or empty cells in any spreadsheet and right here we’ve enlisted all of them with the intention to use them as per your necessities.
Microsoft Excel and Google Sheets assist numerous features in order that customers can carry out varied jobs. There are some features referred to as COUNTBLANK, COUNTIF, SUMPRODUCT, and so forth. that may allow you to to count blank or empty cells. Generally, it’s possible you’ll want to count all empty cells in a spreadsheet. If it has two or three columns and ten or twenty rows, you possibly can calculate them manually. Nonetheless, the issue begins if you go to count the empty cells of a big spreadsheet. That’s when you should utilize this trick to get the precise variety of blank cells in Google Sheets or Excel.
Count blank or empty cells in Excel or Google Sheets
To count blank or empty cells in Excel or Google Sheets, comply with these steps:
- Open the spreadsheet in Google Sheets or Excel.
- Select the column.
- Click on on a cell the place you need to present the quantity.
- Use COUNTBLANK, COUNTIF or SUMPRODUCT perform.
To be taught extra about these steps and features, proceed studying.
First, you want to open the spreadsheet in Google Sheets or Microsoft Excel. Now you must observe down the columns/rows for which you need to discover the variety of empty cells. It may be one or a number of columns, and it is dependent upon your necessities.
After that, click on an empty cell in your spreadsheet where you want to display the number. Then, enter a function like this-
=COUNTBLANK(A2:D5)
This COUNTBLANK function counts empty cells between A2 and D5. You can change the column/row number as per your needs.
There is another function that does the same job as COUNTBLANK. It is called COUNTIF. This function is handy when users need to count cells containing a specific word, digit or symbol. However, you can use the same function to count the empty cells in Google Sheets as well as the Microsoft Excel spreadsheet.
To use this method, you need to open a spreadsheet, select a cell, and enter this function-
=COUNTIF(A2:D5,"")
You need to change the range according to your requirements. The COUNTIF function requires a value between inverted commas. As you are going to find blank cells, there is no need to enter any value or text.
The main difference between COUNTBLANK and COUNTIF functions is that the first one is mainly used to find blank cells whereas the latter one is used to find almost anything.
The third function is SUMPRODUCT. Although it is quite different from other functions because of its characteristics, you will get the job done with the help of the SUMPRODUCT function.
As usual, you need to select a cell where you want to display the number and enter this function-
=SUMPRODUCT(--(A2:D5=""))
You need to change the range before entering this function and do not write anything between the inverted commas.
Hope this tutorial helps you.
Read: How to remove Blank Cells from Microsoft Excel spreadsheet
How do you count empty cells in a sheet?
There are three functions you can use to count empty cells in a sheet. They are: COUNTBLANK, COUNTIF, and SUMPRODUCT. Although each formula works in a different way, you can find the result without any error if you can enter the cell range correctly. The easiest way to count blank or empty cells in a spreadsheet is by using the COUNTBLANK function. Enter the function like this: =COUNTBLANK(A2:D5) where A2/D5 is the cell range.
How do I check for blanks in Excel?
To verify for blanks in Excel, you should utilize a easy perform referred to as ISBLANK with IF. In easy phrases, the IF perform is used to verify whether or not a sure situation matches or not. However, ISBLANK denotes the place it says – it finds the blank cell. So as to use this components, you want to nest them like this: =IF(ISBLANK(A1),”Blank”,”Not Blank”) the place A1 is the cell to discover whether or not it’s blank or not. for apparent causes, you want to apply this components in a distinct column.
Learn: How to count Nonblank Cells in Excel
