How to Count Non-Blank Cells in Google Sheets

Rate this post
How to Count Non-Blank Cells in Google Sheets

Although it may seem to be unnecessary at first, knowing the precise amount of cells filled with data may be quite valuable if you use Google Sheets for your spreadsheets.

The approaches described in this article are often used to count the number of inhabited cells within a certain range. This is especially important for anyone dealing with huge spreadsheets who need to understand the breadth and depth of their data.

Ways to Count Cells That Aren’t Blank in Google Sheets

Here are two quick ways to count the cells in your spreadsheet that aren’t blank. In Google Sheets, use the COUNTA formula to count the number of cells that are not blank. COUNTA is an abbreviation for count all, and it is used to return the number of values in a data collection.

Syntax for COUNTA

Let’s start with the syntax before we look at some COUNTA function samples. It is:

=COUNTA(val1, val2, ...)

Here are the arguments used in the formula:

  • val1: This argument is essential for the formula to function and represents the first range or value in the set to be counted.
  • val2: This argument is the same as val1, except it might specify a different cell range. This parameter is not required.

Here are a few things to know about this function:

  • COUNTA may accept a total of 30 parameters, implying that the val parameters can be as high as val30.
  • This function will count all of the values in the dataset, even those that occur several times. This includes zero-length strings and white space. Cells must be fully empty.

An Example of Using COUNTA to Count Cells That Aren’t Blank in Google Sheets

To show this function, we will take a collection of data in a column with six cells containing values and four cells being empty. To use this formula in Google Sheets, follow these steps:

  1. Choose the cell into which you wish to enter the formula.
  2. Enter the first portion of the COUNTA formula, =COUNTA(
  3. Now specify the address of the range where you want the data to be counted. It is the cell range A2:A11 in this case.
  4. Enter a closing bracket.
  5. Press Enter.
  How to Make More Money With Google Opinion Rewards

Although the COUNTA method produced proper results in the above example, hidden characters in the data, such as apostrophes and spaces, will still be tallied.

This isn’t ideal if you’ve been dealing with numbers as text strings and have mistakenly left some apostrophes or spaces in cells marked “blank.” The same data is shown here, but with spaces or apostrophes in the white space cells.

Using the COUNTIF Formula Instead of COUNTA

This function resolves the issue with the COUNTA formula. We often find ourselves making little typos while typing. On sometimes, we may inadvertently press the spacebar. When we use COUNTA in this case, it will add the cell with only the space, despite the fact that it is regarded empty.

COUNTIF, rather than COUNTA, will yield a more accurate count of the cells that are not blank in these circumstances. COUNTIF is one of numerous IF functions available in Google Sheets.

Syntax for COUNTIF

Let’s look at the syntax before we look at COUNTIF in action. It is:

=COUNTIF(range, criteria)

Here are the parameters used in the formula:

  • range: This is the cell range that will be compared to the values in the criterion parameter.
  • In the range parameter, this is the test or pattern to apply to the cell range.

Here are some details concerning this function: Criteria, like in Excel, may utilize wildcards, such as:

  • “?” to represent a single character
  • Any number of characters may be matched by “*.” For example, “b*” might stand for bob, billy, by, because, and so on. It is also applicable to numerical characters.
  • Add a “” before the character to match a genuine asterisk or question mark.
  Google Promises to Update All Nest Displays and Speakers to Support Matter

If the criteria include a string, it should be enclosed in quotation marks.

Example of Using COUNTIF to Count Not Blank Cells in Google Sheets

We’ll use a collection of data with six cells each containing a value to show this function. Two of the cells in the range include an apostrophe or a space, while the third is vacant.

Here are the steps to take in Sheets to run a formula that ignores spaces and apostrophes:

  1. Click the cell into which you wish to enter the formula.
  2. Enter the formula’s first section, which is =COUNTIF(
  3. Enter the cell range whose values you wish to count in the first argument. It is the cell range A2:A11 in this case.
  4. To split the parameters, use a comma.
  5. The criterion will be added to the second parameter. In this scenario, we use the symbol “*.” Remember to use quote marks as well.
  6. To finish the formula, add a bracket.
  7. To execute the formula, press the Enter key.

When you run the formula, you will get the right number of cells with values since the formula excludes cells with undesirable characters. Because the “*” wildcard says that the cell must contain a string, whether numerical or text, it does so.

Google Sheets does not recognize spaces and apostrophes as strings unless they are enclosed in quotation marks or preceded by a symbol.

Things to Know About COUNTA and COUNTIF

  • The COUNTA formula should only be used when the data in the cells is perfect, which means there are no extraneous characters in the data, such as an apostrophe or a space.
  • Add the necessary parameters to your spreadsheet before using COUNTIF. This covers the comparison operators, as well as the ampersand and asterisk symbols. By providing these values, the function assures that it will filter out cells that aren’t blank yet seem blank in the spreadsheet.
  • In your spreadsheet, you may use both formulae to determine the number of cells that may contain superfluous data. This may be found by subtracting the COUNTIF value from the COUNTA value.
  • The outputs of these functions may be used in other functions in Google Sheets, such as RANDBETWEEN.
  How to Use Google Messages for Web

Keep on Counting

While we just scratched the surface of the COUNTA and COUNTIF formulae for counting not blank cells, there is still a lot to learn about all of Google Sheets’ counting features. In fact, COUNTIF has a sibling function, COUNTIFS, which may be somewhat difficult but is a great one to master.

You are looking for information, articles, knowledge about the topic How to Count Non-Blank Cells in Google Sheets on internet, you do not find the information you need! Here are the best content compiled and compiled by the team, along with other related topics such as: Google.

Similar Posts