Count if NOT Blank in Google Sheets: 4 Simple Methods

Google Sheets has some great formulas that allow you to quickly get information about a dataset.

One of the common things a lot of people need to do often 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.

In this tutorial, I will show you 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.

Count Cells If Not Blank Using COUNTA Function

Google Sheets has a COUNTA function that 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:

You can use the below COUNTA formula to count all the non-empty cells in the dataset:

=COUNTA(A2:A13)

To perform the Google spreadsheet count non empty cells calculation, you would just have to:

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 in case there is a:

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

In all the above 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).

In case you’re sure your data doesn’t have empty strings or apostrophes, you can use the above COUNTA formula, but 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

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 or spaces, or apostrophes.

While you can’t completely rely on the COUNTA formula, here is a simple SUMPRODUCT formula 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/number in it or not. This is checked by the LEN function.

If the length of the characters in the cell is more than 0, it’s counted, else it’s not counted.

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

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

Count if Not Blank With COUNTIF

COUNTIF is often used to count data based on what is inside the cell (counting specific text), but you can also use it for blank cells or, 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. Type =COUNTIF( to start the formula
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 you need to 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, simply click on it and it will show you the COUNT (along with other data such as Average or Max/Min).

Note that it will include all the cells that have anything in it – be it an empty string (=””), apostrophe (‘) or 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:

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 Count if Not Blank Guide

These are the four ways to quickly get the count of cells if not blank in 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)