function in Google Sheets is a simple function which allows for the user to count a range of cells according to one specific filter. If more filters are required for a range, then
would be used instead.
What, though, if the simple criteria to apply to my count is to count all cells that are not blank? How do I annotate in the criterion of the
functions to only count NON-blank cells?
To count only non-blank cells in a range, in the criterion parameter of a
formula use the expression of not blank as
. For example, to count values in
that are not blank write the formula as:
, or if applying multiple criteria where the check for a range not being blank is just one condition use
Count Blank Cells?
to be providing the incorrect result, even though the formula specifically states to only count NON-blank cells.
Why then would it be counting blank cells?
Have a look at the following example:
It can seem like there’s an error with the
function counting only non-blank cells, but there’s a very important thing not evident in the way the “Work Day?” column reports it’s data. It
is a function written as so (for cell B2):
This formula above stating: if the value in the Date column is a weekend (1 = Monday, 2 = Tuesday, etc) then enter the string
, otherwise enter a blank string
If we changed this formula slightly to the following, we get a very different
Notice we don’t return anything if it is a weekend date? What result do we get?
In summary, COUNTIF will count blank cells if these are empty string “” cells, in other words they have the appearance of being “blank” (empty), but in fact are not. Therefore, take consideration to any formulas where a result is returning an empty string and whether this is intended, or if the result actually needs to be genuinely blank.
To apply a filter in either the
functions to count cells that are not blank, use the filter expression
in the criterion parameters for each formula. For example,
=COUNTIFS(Range1, "<>", Range2, "<>")
Be careful with formulas returning an empty string (
) as this will be seen as a NON-blank cell and will display an unexpected result with