Counting non blank cells

J

jack evans

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.
 
P

Peo Sjoblom

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

--


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

Regards,

Peo Sjoblom

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

Harlan Grove

alk said:
=counta(A1:A100)
Click to expand...
....

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.
 
B

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
 
Top