Google Sheets has some great formulas that allow you to quickly get information about a dataset.
One of the common things a lot of people need to do often is to count cells that are not blank in a dataset in Google Sheets (i.e., count non-empty cells).
While it can be done manually if you have a small dataset, for large ones, it’s best to leave this to the awesome COUNT formulas in Google Sheets.
And again, there are multiple ways to skin this cat.
In this tutorial, I will show you two really simple formulas to count cells if not blank in Google Sheets.
Count Cells If Not Blank Using COUNTA Function
Google Sheets has a COUNTA function that counts all the non-empty cells in a dataset (i.e., all the cells that have any text string or number in it).
Suppose you a dataset as shown below and you want to count the non-empty cells in it.
You can use the below COUNTA formula to count all the non-empty cells in the dataset:
While COUNTA formula gives the right result, in this case, it may give you a wrong result in case there is a:
- Space character
- Null/empty string (=””).
- Apostrophe (‘)
In all the above cases, it may look like the cells are empty, but the COUNTA function would still consider these as non-blank and count these in the result.
Below is an example where I have an empty string (=””) in cell A4 and an apostrophe in cell A8. You can see that the result of the COUNTA formula is 11 (although only nine cells are filled with names – or at least that’s what appears to a naked eye).
In case you’re sure your data doesn’t have empty strings or apostrophe, you can use the above COUNTA formula, but in case there is a possibility of it, it’s better to use a combination of formulas to get this done (covered in the next section).
Count Non-Empty Cells Using SUMPRODUCT Function
It’s wonderful how easily you can solve complex problems with a simple combination of formulas in Google Sheets.
When it comes to counting non-empty cells in a dataset, there could be cells that have null string or spaces or apostrophe.
While you can’t completely rely on the COUNTA formula, here is a simple SUMPRODUCT formula that will give you the right result in all the scenarios:
The above formula checks whether the cell has at least one character/number in it or not. This is checked by the LEN function.
If the length of the characters in the cell is more than 0, it’s counted, else it’s not counted.
This takes care of two scenarios – null strings and apostrophe.
And the TRIM part of the function makes sure that if there are space characters in the cells, these are ignored as well.
[Quick TIP] Get Count Value in the Task Bar
If you quickly want to quickly get the count of non-blank cells, you can get that from the taskbar.
All you need to do is select the cells in which you want to get the count of cells that are not blank and see the COUNT value in the taskbar (in the bottom-right part of the Google Sheets document).
In case there are numbers in your dataset, by default the taskbar will show you the SUM and not the COUNT. In that case, simply click on it and it will show you the COUNT (along with other data such as Average or Max/Min).
Note that it will include all the cells that have anything in it – be it an empty string (=””), apostrophe (‘) or space character.
These are the three ways you can use to quickly get the count of cells if not blank in Google Sheets.
You may also like the following Google Sheets tips and tutorials: