How to Count Non-Blank Cells in Google Sheets
It might seem a bit pointless initially, but knowing the exact number of cells filled with data can be extremely useful if you use Google Sheets for your spreadsheets.
Sign up forfree
Forgot your password?
Create an account
*Required: 8 chars, 1 capital letter, 1 number
By continuing, you agree to thePrivacy PolicyandTerms of Use.You also agree to receive our newsletters, you’re able to opt-out any time.
Ways to Count Cells That Aren’t Blank in Google Sheets
Here are two easy methods to count the cells that aren’t blank in your spreadsheet. Using the COUNTA formula to count not blank cells in Google Sheets. COUNTA stands for count all, and you use it to return the number of values contained in a data set.
Syntax for COUNTA
Before we look at some examples of the COUNTA function, let’s take a look at the syntax. It is:
Here are the arguments used in the formula:
Here are a few things to know about this function:
An Example of Using COUNTA to Count Cells That Aren’t Blank in Google Sheets
To demonstrate this function, we use a set of data in a column where six cells contain a value, whereas four are empty. Follow these steps to execute this formula in Google Sheets:
Although the COUNTA formula gave us correct results in the above example, if there are hidden characters in the data like apostrophes and spaces, they will still be counted.
This is not ideal if you’ve been working with numbers as text strings and accidentally left some apostrophes or spaces in cells that are meant to be “blank.” Below is an example of the same data but with spaces or apostrophes in the white space cells.
Using the COUNTIF Formula Instead of COUNTA
This function solves the problem associated with the COUNTA formula. A lot of times, we may find ourselves making little errors when typing. Occasionally, we may accidentally press the spacebar. If we use COUNTA here, it will add the cell with just the space, even though it is considered empty.
In these cases, using COUNTIF instead of COUNTA will provide a more accurate count of the cells that are not blank. COUNTIF is one of the manyIF functions inside Google Sheets.
Syntax for COUNTIF
Before we take a look at COUNTIF in action, let’s take a look at the syntax. It is:
Here are the parameters used in the formula:
Here are a few things to know about this function: Criteria can usewildcards like in Excel, such as:
If the criteria contain a string, enclose it in quotation marks.
Example of Using COUNTIF to Count Not Blank Cells in Google Sheets
To demonstrate this function, we are using a set of data where six cells contain a value. Two of the cells in the range have an apostrophe or space, and one is blank.
Here are the steps you need to follow to execute a formula that will ignore the spaces and apostrophes in Sheets:
When you execute the formula, you will obtain the correct amount of cells with a value in them, as the formula ignores the cells containing any unwanted characters. It does so, as the“*”wildcard specifies the cell must contain a string, whether numerical or text.
Google Sheets does not recognize spaces and apostrophes as strings unless they’re contained within quotation marks or have a~symbol before them.
Things to Know About COUNTA and COUNTIF
Keep on Counting
While we touched the surface of the COUNTA and COUNTIF formulas to count not blank cells, there is still much to learn about all the functions that count in Google Sheets. In fact, COUNTIF has a brother function, COUNTIFS, which can get very complex, but it’s a fantastic one to learn.
Want to count cells based on a condition in Google Sheets? Here’s how to use the COUNTIF and COUNTIFS functions for this.
The key is not to spook your friends with over-the-top shenanigans.
One casual AI chat exposed how vulnerable I was.
You’ve been quoting these famous films wrong all along!
You can block out the constant surveillance and restore your privacy with a few quick changes.
Every squeak is your PC’s way of crying for help.
