# 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:

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

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:

``=IF(WEEKDAY(A2,2)<6,"Y","")``

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:

``=IF(WEEKDAY(A2,2)<6,"Y",)``

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:

## 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:

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:

``=COUNTA(B2:B)-COUNTIF(B2:B,"<*")``

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:

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:

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.

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.