With the help of Google Sheets, users may easily organize and analyze data, also counting the number of non-blank cells in a particular range is a frequent activity when working with data. Knowing how many cells contain information might be helpful when managing a project, conducting surveys, or analyzing sales data.
For many reasons, knowing how to count non-blank cells in Google Sheets is important. It enables users to determine whether their data is complete, locate missing data, and carry out a number of calculations without using empty cells.
This article will go through four easy ways to count if NOT blank in Google Sheets.
How to Count if NOT Blank Cells in Google Sheets?
There are multiple ways to count the non-empty cells, but the most convenient methods are:
- Using the COUNTA function
- Using the COUNTIF function
- Using the SUMPRODUCT function
- Using the combination of the functions
All these methods are explained with the example of the “Employees Information” Google Sheet:
The above sheet has 18 non-empty cells which will count with the help of the above methods.
Method 1: Count if NOT Blank Cells Using the COUNTA Function
The first method of counting the non-empty cells is by using the COUNTA function and to use it, follow the general syntax:
=COUNTA(range)
In the above general syntax, replace the “range” with the actual range of the cell in which it is supposed to find out the non-empty cell. For example, in our case, the “range” of the cells is A2 to D8:
=COUNTA(A2:D8)
The output of the applied function is 18:
Method 2: Count if NOT Blank Cells Using the COUNTIF Function
The second method to find the not blank or non-empty cells is by using the COUNTIIF function following the general syntax:
=COUNTIF(range, “<>”)
In the above formula, use the function of COUNTIF, replace the “range” with the actual range of cells, and then in criteria, use the “<>” for non-empty cells. In our case, the formula of the COUNTIF will be:
=COUNTIF(A2:D8, “<>”)
The result of the COUNTIF formula will again be 18:
Method 3: Count if NOT Blank Cells Using the SUMPRODUCT Function
To count the NOT blank cells in Google Sheets, we can use the SUMPRODUCT function by replacing the range with the actual range of cells in the below-mentioned syntax:
=SUMPRODUCT(--(range<>""))
For example, in our case, the range is A2:D8 so replacing the range in the above formula:
=SUMPRODUCT(--(A2:D8<>""))
The output of the SUMPRODUCT formula will again be 18:
Method 4: Count if NOT Blank Cells Using the Combination of Functions
The last method of counting the NOT blank cells in the Google Sheets is by using the combination of the “IF”, “ARRAYFORMULA()”, and the “ROW” functions. To follow this method, use the syntax:
=ARRAYFORMULA(SUM(IF(LEN(range)>0,1,0)))
In the above formula, it will count the non-empty cells with the “IF” function, and then use the ARRAYFORMULA() function. For example, if the range is replaced with the A2 to D8:
=ARRAYFORMULA(SUM(IF(LEN(A2:D8)>0,1,0)))
When the ENTER key is pressed the count of the NON-BLANK cells is displayed:
Conclusion
To count the NON BLANK cells in Google Sheets, use any of the functions that are COUNTA, COUNTIF, SUMPRODUCT, and a combination of the functions. In this blog, all the mentioned methods are explained with the help of the example, to count the non-empty cells in Google Sheets.
