explain countA please

[Harvey Waxman]

In a cell out of the range:

=COUNTA(F6:F13,"")
=COUNTA(F6:F13,"=""")
=COUNTA(F6:F13,"<>''")

There are eight cells in the range 6:13

If the eight cells are blank I get "1" as the result of either formula.

When enter something in the range, I get 1+ the number of entries.

Obviously I don't know what I'm doing but it sure looks right. Can you explain
this?

So, what is the easiest way to show the number of non-blank cells in a range?
And how can I make sure cells that appear blank are really blank? Deleting the
blank entry doesn't always work.

 

[JE McGimpsey]

In a cell out of the range:

=COUNTA(F6:F13,"")
=COUNTA(F6:F13,"=""")
=COUNTA(F6:F13,"<>''")

There are eight cells in the range 6:13

If the eight cells are blank I get "1" as the result of either formula.

When enter something in the range, I get 1+ the number of entries.

Obviously I don't know what I'm doing but it sure looks right. Can you
explain
this?

So, what is the easiest way to show the number of non-blank cells in a range?

If you have a cell with a formula (that returns a null string), or if
you have a space character instead of a blank, COUNTA will return 1.

One way to take space characters into account:

=SUMPRODUCT(--(LEN(TRIM(F6:F13))>0))

And how can I make sure cells that appear blank are really blank? Deleting
the blank entry doesn't always work.

How do you delete it? With the delete key or with the spacebar?

Alternatively, you could use Edit/Clear/All, which has CTRL-b as the
default keyboard shortcut.

 

[Peo Sjoblom]

=SUMPRODUCT(--(F6:F13<>""))

COUNTA will count non blank cells plus cells that holds ""
so if you have a blank as a result of something like

=IFA1>0,1,"")

then it will be included

 

[Harvey Waxman]

Thanks everyone

If you have a cell with a formula (that returns a null string), or if
you have a space character instead of a blank, COUNTA will return 1.

One way to take space characters into account:

=SUMPRODUCT(--(LEN(TRIM(F6:F13))>0))


How do you delete it? With the delete key or with the spacebar?

Alternatively, you could use Edit/Clear/All, which has CTRL-b as the
default keyboard shortcut.