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.

The techniques explained in this article are most commonly used to count the number of populated cells in a specific range. This is particularly useful for people working with large spreadsheets that need to get an idea of the span and scope of their data.

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: 

=COUNTA(val1, val2, ...)

Here are the arguments used in the formula:

  • val1: This parameter is required for the formula to work, and it is the first range or value in the set to consider when counting.
  • val2: This parameter is the same as val1, but can be used to represent another cell range. This parameter is optional.

Here are a few things to know about this function:

  • COUNTA can take a total of 30 parameters, which means the val parameters can go up to val30.
  • This function will count all of the values in the dataset, including the values that may appear more than once. This also includes zero-length strings as well as white space. The cells must be completely blank.

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:

Using the COUNTA Formula to Count Not Blank Cells
  1. Select the cell you want to input the formula.
  2. Enter the starting part of the COUNTA formula, which is =COUNTA(
  3. Now enter the address of the range where you want to count the values. Here, it is the cell range A2:A11.
  4. Enter a closing bracket.
  5. Press Enter.

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.

COUNTA with invisible data

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 many IF 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: 

=COUNTIF(range, criteria)

Here are the parameters used in the formula:

  • range: This is the cell range to be tested against the values in the criteria parameter.
  • criteria: This is the test or the pattern to apply to the cell range in the range parameter.

Here are a few things to know about this function: Criteria can use wildcards like in Excel, such as:

  • “?” to match a single character
  • “*” can match any number of characters. For example, “b*” could be: bob, billy, by, because, etc. It also works for numerical characters.
  • To match with an actual asterisk or question mark, add a “~” before the character.

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:

Using COUNTIF instead of COUNTA
  1. Click the cell where you want to input the formula.
  2. Enter the starting part of the formula, which is =COUNTIF(
  3. For the first argument, type in the cell range you want to count the values of. Here, it is the cell range A2:A11.
  4. Enter a comma to divide the parameters.
  5. For the second parameter, we are going to add the criteria. In this case, we write it as "*". Make sure to add quotation marks as well.
  6. Add a bracket to complete the formula.
  7. Press the Enter key to run the formula.

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

  • The COUNTA formula is best used only when the data in the cells is perfect, meaning there are no unnecessary characters in the data, such as an apostrophe or a space.
  • When using COUNTIF in your spreadsheet, add the needed parameters. This includes the comparison operators, the ampersand symbol, and the asterisk. Providing these values ensures that the function will filter out the cells that aren't blank but look blank in the spreadsheet.
  • You can use both formulas in your spreadsheet to find the number of cells that might contain unnecessary values. You can find this by subtracting the COUNTIF value from the COUNTA one.
  • You can use the results from these inside other functions such as RANDBETWEEN in Google Sheets.

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.