How to Count Cells If Not Blank in Google Sheets

Sometimes you may have a dataset where some of the cells or rows are blank. In such a case, if you want to count cells that are not blank, then you need to rely on Google Sheets count functions.

In this tutorial, I will show you how to count cells if not blank in Google Sheets.

Count Cells If Not Blank Using COUNTA Function

COUNTA function in Google Sheets counts all the 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 count cells that are not blank, you can use the COUNTA function.Count Cells If Not Blank in Google Sheets - COUNTA

While the COUNTA function works well in most of the cases, 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):

Count Cells Non Blank in Google Sheets - empty string

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 COUNTA function.

Hence, the result 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 the number of cells when a specified condition is met

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


Count Cells If Not Blank in Google Sheets - Countif

Note that this formula ignores blank cells that contain an apostrophe or empty string.

This formula would also ignore Logical Values (TRUE/FALSE) and Errors.

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


You May Also Like the Following Google Sheets Tutorials:


10 Google Sheets Tips that will save you hours every week

Bar Sparkline in Google Sheets - rtl
Google Sheets

Sparkline in Google Sheets – The Only Guide You Need

Sparklines are miniature charts that you can create within a cell. These are quite useful when you’re creating a dashboard and want to quickly show a trend, seasonal increase or decrease, or outliers (maximum/minimum) visually. According

Google Sheets

How to Insert Timestamp in Google Sheets

Google Sheets is a great tool when you’re collaborating and working with people on the same sheet. It allows people to work in the sheet simultaneously. In this tutorial, you’ll learn various to insert timestamp in Google

Google Sheets Keyboard Shortcuts - Spreadsheets
Google Sheets

101 Google Sheets Keyboard Shortcuts

Google Sheets is a relatively new spreadsheet tool in the market. It has quickly gained a lot of fan following and I see a lot of people migrating from other spreadsheets applications to Google Sheets.