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:
=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:
-
I can amend my formula in column
Bso that theFALSEcondition in myIFfunction returns nothing; or - 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:
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:
=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:
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:
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.
