Counting non blank cells

[Guest]

I want to count all the cells in a range that have text in them - I do not want to include any blank cells OR where a formula has returned an empty string. Counta will still count these cells? Any ideas.

 

[Peo Sjoblom]

=SUMPRODUCT(--(A1:A100<>""))

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

I want to count all the cells in a range that have text in them - I do not

want to include any blank cells OR where a formula has returned an empty
string. Counta will still count these cells? Any ideas.

 

[Harlan Grove]

=SUMPRODUCT(--(A1:A100<>""))

Or =COUNTIF(A1:A100,">*")

 

[alk]

=counta(A1:A100)

-----Original Message-----
...

Or =COUNTIF(A1:A100,">*")

 

[Harlan Grove]

=counta(A1:A100)

....

Reread the OP: "I do not want to include any blank cells OR where a formula
has returned an empty string. Counta will still count these cells?"

COUNTA *does* include cells evaluating to "" in its result, so fails to
satisfy the OP's requirements.

 

[Berend Botje]

You can also do this with 2 for... next statements:

Sub BB

Dim Col as integer
Dim Row as integer
Dim CountingValue as integer

For Col = 1 to 100
Row = 1
For Row = 1 to 100
If trim$(len(cells(row,col).value))>0 then CountingValue =
CountingValue +1
End if
Next Row
Next Col

end sub