Google Sheets COUNTIF Not Blank: 4 Easy Ways

Google Sheets has some great formulas that allow you to quickly get information about a dataset. One of the common things that I often need to do is to count cells that are not blank in a dataset in Google Sheets (i.e., count non-empty cells).

While it can be done manually if you have a small dataset, for large ones, it’s best to leave this to the awesome COUNT formulas in Google Sheets. And again, there are multiple ways to skin this cat.

You can use COUNTA and the Google Sheets COUNTIF not blank, or use other functions and features such as SUMPRODUCT and the taskbar’s Count Value.

In this tutorial, I will show you, from experience, two really simple formulas to count cells if not blank in Google Sheets. Follow this Google Sheets ‘count if not blank’ guide to learn more.

Google Sheets Count If Not Blank Using the COUNTA Function

Using COUNTA is the simplest way to do it, but it can fail if there are cells that only appear empty. The COUNTA function counts all the non-empty cells in a dataset (i.e., all the cells with any text string or number).

Suppose you have a dataset, as shown below, and you want to count the non-empty cells. Here’s how to count non-empty cells in Google Sheets:

Here, I used the below COUNTA formula to count all the non-empty cells in the dataset:

=COUNTA(A2:A13)

Here’s what I did to perform the Google spreadsheet count non-empty cells calculation:

1. Select an empty cell
2. Type =COUNTA(
3. Highlight the range you wish to count
4. Press “Enter”

While the COUNTA formula gives the right result, in this case, it may give you a wrong result if there is a:

• Space character
• Null/empty string (=””)
• Apostrophe (‘)

An empty string may be a result of a formula, and a lot of people use apostrophes while entering numbers (to show these numbers as text). In all of these cases, it may look like the cells are empty, but the COUNTA function would still consider these as non-blank and count these in the result.

Below is an example where I have an empty string (=””) in cell A4 and an apostrophe in cell A8. You can see that the result of the COUNTA formula is 11 (although only nine cells are filled with names – or at least that’s what appears to the naked eye).

If you’re sure your data doesn’t have empty strings or apostrophes, you can use the above COUNTA formula. Still, in case there is a possibility of it, it’s better to use a combination of formulas to get this done (covered in the next section).

Related Reading: Google Sheets NOT Function

Count Non-Empty Cells Using SUMPRODUCT Function

Using SUMPRODUCT is a more complicated method, but it is more reliable if you aren’t sure whether some cells are actually empty or have apostrophes.

It’s wonderful how easily you can solve complex problems with a simple combination of formulas in Google Sheets. When counting non-empty cells in a dataset, there could be cells with null strings, spaces, or apostrophes.

While you can’t completely rely on the COUNTA formula, here is a simple SUMPRODUCT formula I use that will give you the right result in all the scenarios:

=SUMPRODUCT(LEN(TRIM(A2:A13))>0)

Here’s how to count filled cells in Google Sheets with this formula:

1. Select an empty cell
2. Type =SUMPRODUCT(LEN(TRIM(
3. Highlight the range you wish to count
4. Close the formula with ))>0)

Here’s how this “count if not empty” Google Sheets method works:

The above formula checks whether the cell has at least one character or number. The LEN function checks this. If the length of the characters in the cell is more than 0, it’s counted, otherwise it’s not counted.

This takes care of two scenarios — null strings and apostrophes.

The TRIM part of the function ensures that if there are space characters in the cells, these are also ignored.

If you have error values in the cells, this formula will give you an error.

Related Reading: Count Cells Based on Color in Google Sheets

Google Sheets COUNTIF Not Blank

COUNTIF is often used to count data based on what is inside the cell (counting specific text), but you can also use it as a Google Sheets COUNTIF, not blank formula. Let’s take a look at a COUNTIF, not blank Google Sheets function.

Here is the example of a Google spreadsheet COUNTIF not blank formula:

=COUNTIF(A2:A8,"<>")

This Sheets COUNTIF, not blank formula, works by using the “<>” operator to include everything that has any text in it while counting.

To make your own similar Google Sheet COUNTIF not blank formula, here’s what you’d do:

1. Click an empty cell
2. Start the formula by typing =COUNTIF(
3. Highlight the range you wish to count
4. Type a comma (,)
5. Type “<>” (don’t forget the quotes)
6. Press “Enter”

[Quick TIP] Get Count Value in the Task Bar

If you want to quickly count non-blank cells in Google Sheets, you can get that from the taskbar.

All I do is select the cells in which you want to get the count of cells that are not blank and see the COUNT value in the taskbar (in the bottom-right part of the Google Sheets document).

In case there are numbers in your dataset, by default, the taskbar will show you the SUM and not the COUNT. In that case, click on it, and it will show you the COUNT (along with other data, such as Average or Max or Min).

Note: It will include all the cells that have anything in it — be it an empty string (=””), apostrophe (‘), or a space character.

Frequently Asked Questions

Can Google Sheets Count If Not Blank?

Yes, you can use the COUNTA, COUNTIF, or SUMPRODUCT functions to count non-empty cells in Google Sheets. The simplest of these methods for Google Sheets to count non-empty cells is COUNTA. To use it:

1. Type =COUNTA( into a blank cell
2. Highlight the range you wish to count
3. Press “Enter”

But you could also use COUNTIF for Google Sheets to COUNTIF, not empty cells:

1. Type =COUNTIF( into a blank cell
2. Highlight the range you wish to count
3. Type “<>”
4. Press “Enter”

Wrapping up the Google Sheets COUNTIF Not Blank Guide

These are the four ways to get the count of cells if not blank in Google Sheets. The Google Sheets COUNTIF not blank is a pretty simple and reliable method, but exploring the other methods is also a good idea to confirm the accuracy of your results.

Alternatively, if you want to upskill your Google Sheets knowledge, then check out this comprehensive Google Sheets formulas and functions masterclass. It covers all the basic functions, including COUNTIF and SUMIF, how to format text strings, and how to validate emails, numbers, and URLs. By the end of this two-hour course, you’ll have all the knowledge you need to use master Google Sheets.

You may also like the following Google Sheets tips and tutorials:

• How to Count Cells with Specific Text In Google Sheets
• How to Get the Word Count in Google Sheets
• How to Separate First and Last Name in Google Sheets
• Character Count in Google Sheets
• IFS Function in Google Sheets – Test Multiple Conditions (Examples)
• How to Use SUMIF function in Google Sheets? Examples!
• ISNUMBER Function in Google Sheets (Check if Cell Contains Number)