Google Sheets Count If Not Blank: Best Approach

How do you count a range of cells and exclude counting empty or blank ones in Google Sheets?

The easiest approach to count cells that are not blank is to use the COUNTA() function. The COUNTA() function has the following parameters:

=COUNTA(value1, [value2...])

The COUNTA() Google Sheets function takes one or more values and counts those values that do NOT have any content. However, just because a cell may look like it doesn’t contain content it may still be added to the count .

Besides the COUNTA() function, there are two other popular approaches, namely, the COUNTIF() and its more capable alternative COUNTIFS() .

The COUNTIF() function in Google Sheets is a simple function that allows the user to count a range of cells according to one condition . The COUNTIF function has the following syntax:

=COUNTIF(range, condition)

If you want to exclude blank cells in your range, the condition parameter would need to be the exclusion of blanks which can be annotated as "<>" .

Therefore your formula would look like this:

=COUNTIF(A1:A10, "<>")

As the COUNTIF function only contains one condition parameter if more conditions are required for your range, then you will want to upgrade your formula to the COUNTIFS Google Sheets function instead, as the COUNTIFS function can take multiple conditions:

=COUNTIFS(range, condition1, [condition2...])

Count Functions Not Working

If you use any of the above solutions such as COUNTA or COUNTIF(range, "<>") or COUNTIFS(range, "<>") you may be pleasantly surprised that your formula is giving you an incorrect result.

Have a look at the following example where the COUNTA formula is used on a simple range that only contains a handful of cells with Y values:

COUNTA doesn’t appear to be working, why?

You can see the same behaviour occurring when using the COUNTIF formula too:

COUNTIF result should be 5 not 7, why is that??

The reason why these formulas do not work is by inspecting the values in column B which both functions are referencing.

When inspecting cell B2 in this spreadsheet I noticed that the formula was:


This formula simply calculates whether the Date value was a weekend (1 = Monday, 2 = Tuesday, etc) and if it wasn’t enter the string "Y" , otherwise enter a blank string "" .

And this is the issue: I’ve entered something to represent what to do when it is a weekend, and I’ve entered a blank string .

Therefore, the COUNTA and COUNTIF(range, "<>") formulas will detect that there is something in that cell and will count it.

I can perform one of two things:

  1. I can amend my formula in column B so that the FALSE condition in my IF function returns nothing; or
  2. I can refine the count formula instead.

Amend Formulas To Return BLANKS

If you choose option 1 and have the ability to amend the formula used by your count functions then you would simply change your formula to the following:


Notice we don’t return anything in the FALSE condition of the IF statement. This is now setting a BLANK value in those cells.

And here’s what you now would see:

A correct result in our COUNTA and COUNTIF(range, "<>") functions

Use COUNTIF/S With Different Conditions

If you aren’t achieving the desired result using the COUNTA() or COUNTIF(range, "<>") or COUNTIFS(range, "<>") formulas then there are a couple of other methods to help.

One popular option is to refine the COUNTIF formula to include the condition ">0"&"*" as follows:

=COUNTIF(B2:B, ">0"&"*")

However, this approach has the following problem – it doesn’t count numbers in cells as shown below:

COUNTIF(range, ">0"&"*") works if your range doesn’t contain numbers

As you can see from the above demonstration the COUNTIF(B2:B, ">0"&"*") works fine if the contents you are counting contain only text, but it doesn’t work well if there are cells containing numbers too.

The method I prefer to use is combining both the COUNTA() and the COUNTIF/S formulas together like so:


The condition in the COUNTIF function of "<*" counts all cells containing less than all – which are your empty string cells.

This produces the following result:

Combining COUNTA and COUNTIF/S to exclude empty string cells

As you can see from the above result, the outcome produces a proper count, including those cells containing text and numbers.

Counting Cells That Are Blank In A Range

If you wanted to count all cells that are blank in a range you can simply modify your COUNTIF/S formula to count those cells by using the condition "" as follows:

=COUNTIF(B2:B, "")

This produces the following result:

Counting just the blank cells in a range

By using the condition "" for your selected range in the COUNTIF formula you can count the number of blank cells.

Google Sheets Count If Not Blank: Summary

To count all cells that do not contain blanks apply a condition in either the COUNTIF or COUNTIFS functions using the filter expression "<>" in the criterion parameter.

If, however, the count range is being performed on cells that return an empty string you can either modify the formula to output blanks by removing "" in your formula, or, you can modify your counting formula by using COUNTA(range)-COUNTIF(range, "<*") .

Be careful with formulas returning an empty string ( "" ) as this will be seen as a NON-blank cell and display an unexpected result with COUNTIF and COUNTIFS formulas.

You can access the Google Sheet used in this blog post here.

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.