COUNTIF not blank is the easiest way to count non-empty cells in Google Sheets. Whether you’re counting completed entries, products, employees, or other data, the COUNTIF function can quickly return the total. However, if you only need to count all non-empty cells without applying criteria, the simpler COUNTA function is often enough.

But what if your data contains formulas that return empty strings (""), cells with only spaces (" "), or imported content with non-printable characters such as tabs and line breaks? These can cause COUNTIF to return unexpected results.

In this tutorial, you’ll learn how to use COUNTIF not blank correctly, when COUNTA is the better choice, and how QUERY and SUMPRODUCT can help you count only real non-blank cells.

COUNTIF Not Blank in Google Sheets

Assume you have sequential dates from 1 July 2026 to 31 July 2026 in the range A2:A32, and the corresponding sales quantities in B2:B32.

To count the number of non-blank cells in column B, use the following COUNTIF formula:

=COUNTIF(B2:B32,"<>")

This returns the number of days on which sales were recorded during the month.

Alternatively, you can use:

=COUNTA(B2:B32)

If you need to count non-blank cells based on one or more conditions—for example, counting non-blank cells in column B only when the corresponding cell in column A is not blank—use the COUNTIFS function instead. For more details, see Using “Not Blank” as a Condition in COUNTIFS in Google Sheets.

Why COUNTIF Not Blank Returns the Wrong Count

If you use COUNTIF not blank or COUNTA on a range containing formulas or data imported from other applications, the result may sometimes be higher than expected.

This is because some cells may contain empty strings (""), spaces, tabs, line breaks, or other non-printable characters. Although these characters may not be visible, the functions still treat those cells as non-blank.

In simple terms, both COUNTIF and COUNTA count any cell for which ISBLANK returns FALSE.

The ISBLANK function checks whether a cell is truly blank.

For example, consider the following formula:

=IF(logical_expression,"Passed","")

If the logical_expression evaluates to FALSE, the formula returns an empty string ("") instead of a truly blank cell. Although the cell appears empty, it is still treated as non-blank by functions such as COUNTIF, COUNTA, and ISBLANK.

Similarly, cells containing characters returned by functions such as CHAR(9) (tab) or CHAR(10) (line break), or imported data with hidden or non-printable characters, are also treated as non-blank.

In short, COUNTIF not blank counts every cell that contains any value or character, whether visible or invisible.

Count Only Real Non-Blank Cells

When your data contains hidden or non-printable characters, there isn’t a one-size-fits-all COUNTIF solution. The best approach depends on the type of hidden content present in the cells.

The question now is how to count only real non-blank cells. Unfortunately, there isn’t a dedicated Google Sheets function for this. Instead, you can combine multiple functions to remove unwanted characters before counting the remaining values.

One effective approach is to use SUMPRODUCT together with CLEAN, TRIM, and LEN:

=SUMPRODUCT(LEN(TRIM(CLEAN(B2:B32)))>0)

Here’s how the formula works:

• CLEAN removes non-printable characters, such as tabs and line breaks.
• TRIM removes leading, trailing, and repeated spaces.
• LEN returns the length of the cleaned text.
• LEN(...)>0 returns TRUE for cells containing real content and FALSE otherwise.
• SUMPRODUCT treats TRUE as 1 and FALSE as 0, then returns the total count.

Another effective alternative is the following QUERY formula:

=ARRAYFORMULA(QUERY(TRIM(CLEAN(B2:B100)),"select count(Col1) label count(Col1) ''"))

Here, CLEAN and TRIM first remove non-printable characters and unnecessary spaces. The cleaned array is then passed to QUERY, which counts the remaining non-blank values.

ARRAYFORMULA is required because TRIM and CLEAN are not array-enabled on their own. It isn’t needed in the SUMPRODUCT version because SUMPRODUCT natively processes arrays.

One final tip: While CLEAN removes ASCII non-printable characters, it doesn’t remove all Unicode whitespace characters. For example, imported web data often contains non-breaking spaces (CHAR(160)), which CLEAN leaves untouched. In such cases, use SUBSTITUTE to replace them before applying TRIM and counting the remaining values.

For example:

=SUMPRODUCT(LEN(TRIM(SUBSTITUTE(CLEAN(B2:B32),CHAR(160),"")))>0)

Frequently Asked Questions

How do I count non-empty cells in a range?

You can use either the COUNTIF or COUNTA function.

• Use COUNTIF(range, "<>") to count non-blank cells.
• Use COUNTA(range) to count all non-empty cells without specifying a criterion.

How do I count non-blank cells in one column based on another column?

Use the COUNTIFS function to count non-blank cells that meet one or more conditions in another column.

Why does COUNTIF or COUNTA return an incorrect or larger count?

The range may contain hidden characters, spaces, or formulas that return empty strings (""). In such cases, use the SUMPRODUCT or QUERY solution described in this tutorial to count only real non-blank cells.

Key Takeaways

• Use =COUNTIF(range,"<>") to count non-blank cells.
• Use COUNTA when you simply need to count all non-empty cells without criteria.
• Use COUNTIFS when you need to count non-blank cells based on one or more conditions.
• Be aware that COUNTIF and COUNTA also count cells containing empty strings (""), spaces, and other hidden characters.
• For imported or inconsistent data, clean the range first or use the SUMPRODUCT or QUERY solutions shown in this tutorial.
• If your data contains non-breaking spaces (CHAR(160)), use SUBSTITUTE together with CLEAN and TRIM before counting.

Conclusion

COUNTIF not blank is the simplest way to count non-empty cells in Google Sheets and works perfectly for most datasets. However, when you’re working with imported data or formulas that return empty strings, hidden characters can produce misleading counts. Understanding these edge cases and knowing when to switch to SUMPRODUCT or QUERY will help you count only the cells that contain meaningful data.

Resources

• How to Count Until a Blank Row in Google Sheets
• Google Sheets: Count Cells Between First and Last Non-Blanks in a Row
• Count Empty or Blank Cells in Rows in Google Sheets