“If” function correctly returns “null” value but Counta function still counts that cell as 1

From the Function Browser article on COUNTA:

A null string is a text value with zero length, meaning a cell containing a null string does 'contain a value,' and will be counted by COUNTA

IF the values in the cells that are to be included the count are Numbers, numeric expressions, or date and time values. consider using COUNT.

COUNT ignores text values, including null strings. t includes zeros, provided the zero value is expressed as a numeral or 'numeric expression.'

COUNTIF, and COUNTIFS count cells containing values that meet a secified condition (COUNTIF) or all conditions in a set of more than one condition (COUNTIFS).

In SGIII's example above, COUNTIF counts all of the cells in the named range (column D, not including the header row or the footer row),meeting the condition that their content is not equal to ( "<>" ) a null string ( "" ).

Given the description in the initial question, SG's take seems to be the best available solution.

Note in the image below that SG's formula omits the cells containing a null string (yellow fill) and omits cells that are empty (grey fill) but does include cells containing the numeral zero (blue-grey fill). Zero is a number, so this is expected behaviour.

D4: COUNTIF(D,"<>"&"")                                    E4: COUNTIF(E,"<>"&"")                           F4: COUNTIF(F,"<>"&"")

Regards,

Barry

Badunit wrote:

the term "blank" should mean the same thing in all situations. Consistency is good. Inconsistency is bad.

Maybe the problem is that "spreadsheetese" (or whatever you want to call it) is a "language" that has developed over time.😀

Languages tend to have inconsistencies, where one term can mean different things depending on the context.

In this context, instead of...

=COUNTA(<range to count>)

... one can simply use:

=COUNTIF(<range to count>,"<>"&"")

As posted way up near the top of this thread.

SG

You can consider using COUNTIF, like this:

=COUNTIF(D,"<>"&"")

This specifically excludes the null strings.

In the example Row 5 is defined as a Footer Row so that it can hold formulas referring to the entire column.

Substitute ; for , in the formula if your regions uses , as a decimal separator.

SG

Hi Almoore,

Barry wrote

A null string is a text value with zero length

An "Ahah!" moment.

A formula in C that inserts NULL if the condition is false:

Column D tests for the length (LEN) of each C result

The Footer Cell in Column D

Regards,

Ian.

SGIII wrote:

... one can simply use:

=COUNTIF(<range to count>,"<>"&"")

As posted way up near the top of this thread.

SG

Which is what I do. I have never used COUNTA or COUNTBLANK. I once tried to use ISBLANK as the condition in a COUNTIF but found that did not work out as expected so I've not used it since.

stfflspl wrote:

Here is the real issue (inconsistency):

COUNTBLANK counts the null string (“”) as empty.

ISBLANK counts the null string(“”) as NOT empty.

Excel is the same and it is the 500 pound gorilla that sets the rules.

Here is the real issue (inconsistency):

COUNTBLANK counts the null string (“”) as empty.

ISBLANK counts the null string(“”) as NOT empty.

Solution: watch out when you are dealing with null strings (""). Sometimes cells containing null strings are considered as empty, sometimes not.

Paul.

In my opinion, the term "blank" should mean the same thing in all situations. Consistency is good. Inconsistency is bad.

ISBLANK says a null string is not blank,

COUNTBLANK says a null string is blank,

COUNTA says a null string is not "empty" (blank)

IF(B1=""...) returns TRUE when B1 is totally devoid of everything or when it has a null string so it says "blank" is the same as a null string.

I am sure we can blame it on Microsoft. Numbers needs to be just as inconsistent as Excel for compatibility.

“If” function correctly returns “null” Counta function still counts that cell as 1

Did it work for you?

SG

Is this a bad thing? It gives you tools as to how to how to evaluate a cell. That said, I don't think I have ever needed to count a cell that had a function in it evaluating to a null string.

quinn