Sometimes when working in spreadsheet programs like Google Sheets, you may have a mix of cells that contain no data (blank cells), and other cells that are not blank (contain data).
You may want to end up counting the number of cells that are not blank within a certain range. In a previous post, I have already shown how to count empty cells.
In this tutorial, I will show you how to count the cells that are not empty in Google Sheets.
Table of Contents
Counting Non-Blank Cells with COUNTA
The easiest way to count the cells that are filled with data and are not blank is with the COUNTA function.
The COUNTA function will return the number of values in a data set. In other words, it counts the number of cells that are not empty.
The syntax of the COUNTA function is:
=COUNTA(value1, [value2, …])
- value1 – The value or range to count the number of values for
- value2 – this argument is optional. You can add additional ranges or values here
Here is an example of how the COUNTA function could be used to count the cells that are not blank:
You can see in the example above, you simply need to place the range into the formula and it will count the cells that contain values. In this example there are 5 cells that are not empty so the formula returns 5.
Counting Non-Blank Cells with COUNT
If you only want to count the cells that contain numeric values, instead of using COUNTA you can use the COUNT function.
The syntax is the exact same, only COUNT will just return the count of cells containing numbers.
Here is an example of how this could be used:
In this example, there are 8 cells that contain values, but 3 of the values are text, so the formula only returns a count of 5 for the cells containing numeric values.
Counting Non-Blank Cells with Text
If your spreadsheet only contains numeric and text values, you can count the number of cells that contain text values by combining the two previous functions like this:
=COUNTA(value1, [value2, …]) – COUNT(value1, [value2, …])
This formula will subtract the non-empty cells containing text values from the total count of non-empty cells, which will end up returning the number of cells with text.
Here is an example of this would look in a spreadsheet:
In the example above, there are 8 cells in the range with values, and 5 of them are numeric values. So the formula subtracts 5 from 8 and returns 3. This is the number of cells with text values.
The only thing to watch out for with this method is that it will not work if you have special characters in your sheet. It will only work if your sheet contains numeric and text values only.
Closing Thoughts
I have shown a few different methods for counting the number of cells that are filled with data in your spreadsheet. Depending on whether you want to count all non-empty cells, just cells filled with numeric values, or just cells filled with text values, you have a few different options to try out.
Try each method in your own spreadsheet so that you become comfortable with using these functions.
More Google Sheets Tutorials:
How to Count Unique Values
How to Count Cells with Specific Text
How to Count Colored Cells