How to Use COUNTIF to Count Cells That Are Not Blank

How do you count not-blank cells in a spreadsheet? I’ll show you. In Google Sheets, the COUNTA and COUNTIF functions allow you to count cells if they’re not blank. You just have to tell them what you’re looking for. I’ll break down these functions’ syntax, and I’ll show you exactly what to enter. That way, you can count non-blank cells in your spreadsheet. Note that this guide applies to both Google Sheets and Excel.

How to Count Not Blank in Google Sheets

While it’s pretty common to use COUNTIF non blank cells in Excel, you’ll want to use the COUNTA function in Google Sheets. This is the simplest way to count non-blank cells. The formula basically does all the work for you. Here’s how:

• 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.

Before we dive in, here’s a quick video explaining how to count not blank cells in Google Sheets. You can also subscribe and it on YouTube. I regularly update that channel with spreadsheet tips, including how to use COUNTA and COUNTIF for not empty cells.

How to Count Cells If Not Blank Using COUNTA Function

Let’s talk about the syntax for the COUNTA function. Here’s what the formula looks like:

=COUNTA(range)

In this example, you’ll just highlight the range of cells you want to evaluate. The return value counts non-blank cells in your chosen range. That means you’ll use the COUNTA function in Google just like you’d COUNTIF not blank cells in Excel. The main difference? This one automatically ignores the blank cells.

Let’s look at an example. If you have a dataset like we have below and you want to Google Sheets to count cells with text, you can use the following steps.

• Click or select a blank cell.
• Input the COUNTA function.
• After the opening brackets, select the range of cells you want to count.

• Click Enter and the formula will count the non-empty cells of your selected range of data.

Limitations of the COUNTA Function

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 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 shows 12 while it should be 10. Let’s talk about how to solve this using COUNTIF for non-blank cells.

How to Use COUNTIF Non Blank Cells

The COUNTIF function can also be used to count non-blank cells. While this formula is best known for its ability to count cells with a specific criteria, it’s also possible to count all the cells that are not blank. Here’s what that kind of formula looks like:

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

In this example, I’ve filled the range as A1:A13. You can select your range in place of that. The rest of the COUNTIF non blank formula shows “>0”, which checks if there’s anything there, and it has an ampersand sign followed by “*”, which makes sure there’s no hidden characters in the cell. Here’s a step-by-step breakdown with screenshots on how to use COUNTIF for non blank cells.

• 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. That resolves the issue we saw earlier with the COUNTA formula. In this case, COUNTIF non blank makes more sense.

Note that 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 the =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.

How to Delete Empty Rows in Google Sheets

I made a video that shows you how to quickly delete empty rows in Google Sheets. If you want to clean up your data, this can be a helpful tool.

Frequently Asked Questions

Here are the most common questions I hear about COUNTIF not blank. Note that some of them also apply to the COUNTA function, which is available in Google Sheets and counts non-blank cells. Please let me know if you have any other 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)

In this case, 9 is the function number for SUM.

If cntifs the same as cntif?

The COUNTIFS function works in a very similar way to the COUNTIF function. The difference is that cntifs returns the sum based on multiple criteria, where cntif only returns the sum of a range based on a single criteria.

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.

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