COUNTIF Not Blank in Google Sheets: An Easy 2023 Guide

Prefer watching a video tutorial? Watch our video on how to count cells if not blank in Google Sheets below:

 

If you’ve ever had a spreadsheet with blank cells and you wanted to count the non blank cells then you must know how impractical it is to count them by eye.

Fortunately, you don’t have to. In such a case, if you want to COUNTIF not empty in Google Sheets, there are a couple of functions you can use.

In this tutorial, we’ll show you how to count cells if not blank in Google Sheets using these functions and the difference between each function.

How to COUNTIF Not Blank in Google Sheets

Here’s how to have Google Sheets count nonempty cells:

• Select a blank cell.
• Type the COUNTA function in the selected cell
• After the opening brackets, select the range of cells you want to count, for example: =COUNTA(A3:A16)
• Press Enter on your keyboard. You now have the total number of cells with values in it.

How to Count Cells If Not Blank Using COUNTA Function

COUNTA function is used like a Google Sheet COUNTIF not blank cells that have any value or text string in it, and ignores the blank cells.

For example, if you have a dataset as shown below and you want to Google Sheets count cells with text, you can use the COUNTA function.

Follow these steps to use the COUNTA function

Step 1: Click or select a blank cell.

Step 2: Input the COUNTA function.

Step 3: After the opening brackets, select the range of cells you want to count.

Step 4: Click Enter and the formula will count the non empty cells of your selected range of data.

While the COUNTA function works well in most of the cases to have Google Sheets count if not blank, it fails to give the right result when there is an empty string in the cell, or there is an apostrophe.

An empty string (“”) can be a result of a formula while apostrophe is often used by many people during data entry.

The empty string and apostrophe make the cell look blank, but are not ignored by the COUNTA function. So if you have a cell that has these, it will be counted by COUNTA function (as shown below):

Note that the cell A3 has an empty string (“”) and cell A7 has an apostrophe (‘). While these look empty, these cells are counted by the Google Sheets COUNTA function.

Hence, the result for Google Sheets COUNTIF not blank is 12, while it should be 10.

Count Cells If Not Blank Using COUNTIF Function

COUNTIF function in Google Sheets can be used to count if not empty Google Sheets when a specified condition is met

To count all the cells that are not blank, you can use the following function:

=COUNTIF(A1:A13,”>0″&”*”)

Follow these steps to use the COUNTIF function

Step 1: Click or select a blank cell

Step 2: Input the COUNTIF function.

Step 3: Select the range of cells you want to count.

Step 4: Add a comma and include the condition.

Step 5: Click Enter and the formula will count the non empty cells of your selected range of data.

Note that the condition, (“>0″&”*”) in this formula ignores blank cells that contain an apostrophe or empty string and any hidden characters.

This formula would also ignore Logical Values (TRUE/FALSE) and Errors. As you can see in our example, cell A3 has a logical values but the cell has not been counted in the formula.

You can also use the below SUMPRODUCT formula for counting non-blank cells in Google Sheets:

=SUMPRODUCT(LEN(A1:A13)>0))

Count Cells if Not Blank Using SUMPRODUCT Function

It is fairly easy to count non-blank cells in Google Sheets using a combination of simple functions. When you want to count non empty cells in Google Sheets, there could be cells with null strings, spaces, or apostrophes.

A Google Sheets user can’t entirely rely on using the COUNTA formula to count non-empty cells.

The COUNTA function counts blank cells but it is not always reliable below is a simple SUMPRODUCT formula that will give you the correct results in all scenarios.

=SUMPRODUCT(LEN(TRIM(RANGE YOU WISH TO COUNT))>0)

Follow the step-by-step instructions and example below to learn how to use this formula in a Google spreadsheet to count cells with text:

Step 1: Choose or select an empty cells.

Step 2: Type the formula =SUMPRODUCT

Step 3: Add the LEN  function.

Step 4: Wrap the following trim formula (TRIM(A2:A11)

Step 5: Add the >0) to return only blank cells.

Step 6: Click Enter on your keyboard to get the results.

This is how Google Sheets counts blank cells using =SUMPRODUCT formula

The formula illustrated above checks whether a cell has at least any type of character or number in it. If not then it’s a blank cell.

If the length of the characters in a cell of your Google Sheets spreadsheet is more than the number 0 it is then counted. However, if the length of characters in a cell is less than 0 then it is not counted. This is checked by the LEN function.

That takes care of null strings and apostrophes scenarios. The purpose of the TRIM function is to ignore space characters in the cell are ignored.

Facts to Know About the Functions

While using the functions to count non blank cells in Google Sheets, there are a couple of things you need to keep in mind about the functions:

• The COUNTA function only works if with perfect values. It won’t work in cells with spaces, logical values or symbols.
• The COUNTIF function will not count non blank cells that contain an empty string or an apostrophe.
• The COUNTIF function also ignores Logical Values (TRUE/FALSE) and Errors.

Frequently Asked Questions

How Do I COUNTIF Not Blank in Google Sheets?

There are several ways you can COUNTIF cells not blank in Google Sheets. The easiest method is using the COUNTA function. All you have to do is type in the formula and select the range you want to count.

Keep in mind that the COUNTA function counts all the non-blank cells, including cells with logical values, apostrophes, and space characters.

We’ve also shown you other functions that you can use to count non blank cells in Google Sheets, including the SUMPRODUCT function.

What is the Difference Between COUNT And COUNTA?

The Count function in Google Sheets returns the number of cells in a range that contain numbers. It ignores blank cells, text values, and cells that contain errors.

On the other hand, The COUNTA function returns the number of cells that are not empty. Unlike the COUNT function, it doesn’t discriminate text, numbers, or logical values.

How Do You Count Cells That Aren’t Blank?

Use the COUNTA function to count cells in a Google Sheet that aren’t blank. However, if you have cells with a blank string or an apostrophe that you want to ignore in the count then use the COUNTIF function instead. The COUNTIF function ignores cells with space characters, symbols and logical values.

You can also use the SUMPRDOUCT function. However, you’ll need to use it with the LEN and TRIM functions. This formula works the same as the COUNTIF function by ignoring symbols and space strings.

How Do I Count Only Visible Cells in Google Sheets?

Sometimes you may have filters or hidden rows, and you want to count only visible cells in Google Sheets. In this case, you can use the SUBTOTAL function.

The formula you should use is =SUBTOTAL(function_number, range). For example, if you wanted to count the number of visible cells in column B you’d use the formula:

=SUBTOTAL(9, B1:B10), where 9 is the function number for SUM.

Wrapping Up

We’ve covered everything you need to know in this COUNTIF Not Blank in Google Sheets guide to get you started. You can now play around with the formulas to suit your needs. If you found this article helpful, you can have a look at our guide to conditional formatting in Google Sheets.

Want to become a Google Sheets expert? We highly recommend checking out Udemy’s Google Sheets Comprehensive Masterclass and the Complete Google Sheets Course. We even have our own course now that details how to master Google Forms and Sheets in one place.

Related:

• Using the VLOOKUP Function in Google Sheets
• Creating a Drop Down List in Google Sheets
• Using IFS Function in Google Sheets
• Using IFERROR Function in Google Sheets
• Using OR Function in Google Sheets