Prefer watching a video tutorial? Watch our video on how to count cells if not blank in Google Sheets below:
Sometimes you may have a dataset where some of the cells or rows are blank. In such a case, if you want to count cells that are not blank, then you need to rely on Google Sheets count functions.
In this tutorial, I will show you how to count cells if not blank in Google Sheets.
Count Cells If Not Blank Using COUNTA Function
COUNTA function in Google Sheets counts all the cells that have any value or text string in it, and ignores the blank cells.
For example, if you have a dataset as shown below and you want to count cells that are not blank, you can use the COUNTA function.
While the COUNTA function works well in most of the cases, it fails to give the right result when there is an empty string in the cell, or there is an apostrophe.
An empty string (“”) can be a result of a formula while apostrophe is often used by many people during data entry.
The empty string and apostrophe make the cell look blank, but are not ignored by the COUNTA function. So if you have a cell that has these, it will be counted by COUNTA function (as shown below):
Note that the cell A3 has an empty string (“”) and cell A7 has an apostrophe (‘). While these look empty, these cells are counted by the COUNTA function.
Hence, the result is 12, while it should be 10.
Count Cells If Not Blank Using COUNTIF function
Countif function in Google Sheets can be used to count the number of cells when a specified condition is met
To count all the cells that are not blank, you can use the following function:
Note that this formula ignores blank cells that contain an apostrophe or empty string.
This formula would also ignore Logical Values (TRUE/FALSE) and Errors.
You can also use the below SUMPRODUCT formula for counting non-blank cells in Google Sheets:
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.