Counting a range of cells with TEXT

[Guest]

If i have the following range, for example:

A
1 apple
2 35.5236
3 abc
4 s
5 625
6 inv

How can I program the NUMBER of cells that returns a text value...(in this
example, the answer would be 4)??

Thank you,

FLKulchar

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A10<>""),--(NOT(ISNUMBER(A1:A10))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[halimnurikhwan]

Hi,

Why you don't use Worksheet function to this with:
=COUNTIF(A1:A6,"apple")

but if you want it programtically use:
sub countsomething()
dim j as long, v
v = 0
for j = 1 to 6
if cells(j,1).value= "apple" then v = v+1
next j
msgbox v
end sub

Regards,

halim


F. Lawrence Kulchar menuliskan:

 

[Leo Heuser]

If i have the following range, for example:

A
1 apple
2 35.5236
3 abc
4 s
5 625
6 inv

How can I program the NUMBER of cells that returns a text value...(in this
example, the answer would be 4)??

Thank you,

FLKulchar

One way:

Sub NumOfTextEntries()
'Leo Heuser, 4 Sept. 2006
Dim Cell As Range
Dim Counter As Long

For Each Cell In Worksheets("Sheet1").Range("A1:A6").Cells
If Application.IsText(Cell.Value) Then Counter = Counter + 1
Next Cell

MsgBox "Number of text entries: " & Counter
End Sub

 

[Guest]

PERFECT..thank you..

I understand it perfectly, but I NEVER could have created it!

Thank you,

FLKULCHAR