You may not have attributed much importance to it, but knowing the exact number of cells that have data can be useful to a great extent, especially if you’re using Google Sheets for your spreadsheets.
The techniques we’ll be showing you in this guide are commonly used to count the number of populated cells in a specific range.
This is usually useful for people who work with large spreadsheets that need to have an idea of the span and scope of their data.
DON’T MISS: HOW TO CREATE A COUNTDOWN TIMER IN GOOGLE SHEETS (EASY STEPS)
In This Article
Ways to Count Cells that are not Blank in Google Sheets
Here are two easy ways to count non blank cells in your spreadsheet. Using the COUNTA formula to count cells that are not blank in Google Sheets.
COUNTA means to count all, and you use it to return the number of values that are in a data set.
Syntax for COUNTA
Before looking at some examples of the COUNTA function, let’s take a peep at the syntax. It is:
| =COUNTA(val1, val2, …) |
Here are the arguments used in the formula:
val1: This parameter is needed 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. This means the val parameters can go up to val30.
This function will estimate all of the values in the dataset, including the values that may show more than once. This also includes zero-length strings and white space. The cells must be entirely blank.
An Example of Using COUNTA to Count Cells That Aren’t Blank in Google Sheets
To illustrate this function, we use a set of data in a column where six cells contain a value, and four are empty. Follow these steps to use this formula in Google Sheets:
Step 1: Select the cell you want to input the formula.
Step 2: Enter the starting part of the COUNTA formula, which is =COUNTA(
Step 3: Now enter the address of the range where you want to count the values. Here, it is the cell range A2:A11.
Step 4: Enter a closing bracket.
Step 5: Press Enter.
Although the results of the COUNTA formula above were correct, if there are unseen characters such as apostrophes and spaces in the data, they will still be counted.
This is not good enough if you’ve been working with numbers as text strings and mistakenly left some spaces or apostrophes in cells that are meant to be empty.
The example below shows the same data but with apostrophes and spaces in the white space cells.
Using the COUNTIF Formula Instead of COUNTA
This function solves the issue relating to the COUNTA formula. Many times, we may find ourselves making little errors when typing.
Occasionally, we may press the spacebar accidentally. 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 give a more accurate count of the nonblank cells. COUNTIF is one of the many IF functions in Google Sheets.
Syntax for COUNTIF
Before taking a look at COUNTIF in action, let’s look at the syntax. It is:
| =COUNTIF(range, criteria) |
Here are the formula’s parameters:
- 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:
- “?” is used to match a single character
- “*” can be used to match any number of characters. For example, “b*” could be bob, billy, by, because, etc. It also works for numerical characters.
- Add a “~” before the character to match with an actual asterisk or question mark.
If the criteria contain a string, enclose it in quotation marks.
DON’T MISS: HOW TO USE DRIVING MODE IN GOOGLE ASSISTANT (STEP-BY-STEP)
Example of Using COUNTIF to Count nonBlank Cells in Google Sheets
To illustrate this function better, we are using a set of data where six cells contain a value. Two of the cells in the range have a space or apostrophe, and one is empty.
Here’s what you should do, to execute a formula that will ignore the spaces and apostrophes in Sheets:
- Click on the cell where you want to enter the formula.
- Input the starting part of the formula, which is =COUNTIF(
- For the first argument, type in the cell range you want to count the values of. It is the cell range A2:A11 here.
- Input a comma to separate the parameters.
- As 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.
- Add a bracket to complete the formula.
- Click on the Enter key to run the formula.
When you execute the formula, you will be given the correct amount of cells with a value in them, since the formula disregards 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 doesn’t recognize apostrophes and spaces as strings unless they’re contained within quotation marks or they have a ~ symbol before them.
DON’T MISS: HOW TO USE MICROSOFT EDGE TO SHARE FILES BETWEEN DEVICES
Things you need to Know about COUNTA and COUNTIF
The COUNTA formula is mostly used only when the data in the cells don’t have errors, which means there are no unnecessary characters in the data, like a space or an apostrophe.
Whenever you’re using COUNTIF in your spreadsheet, remember to always add the needed parameters.
This includes the comparison operators, the ampersand symbol, and the asterisk. Inputting these values will make sure the function filters out the nonblank cells but looks blank in the spreadsheet.
You can also apply both formulas in your spreadsheet to find the number of cells with unnecessary values.
You can find this by subtracting the COUNTIF value from the COUNTA one.
The results from these can be used inside other functions such as RANDBETWEEN in Google Sheets.
DON’T MISS: HOW TO CREATE A CRM IN GOOGLE SHEETS (STEP-BY-STEP)
The bottom line
While we’ve discussed the basics of using COUNTA and COUNTIF formulas to count non blank cells, there is still more to learn about all the functions that count in Google Sheets. COUNTIF has an upgraded function, COUNTIFS, which can get complicated. However, it’s a fantastic one to learn.