PDA

View Full Version : Solved: Count Text



mike31z
06-15-2006, 05:39 PM
How do you count a text in a column.
example in column d Range d9:d50 how many times does is the letter p entered.
I am sure there is an internal excel formula for this (countif doen't work on text) (count doesn't work on text)

any body know the correct FUNCTION Name to complete this task.

mike in wisconsin

acw
06-15-2006, 09:21 PM
Hi

How about


=countif(d9:d50,"*"&"p"&"*")



Tony

johnske
06-15-2006, 11:17 PM
Hi

How about


=countif(d9:d50,"*"&"p"&"*")



TonyThat counts the number of cells that contain at least one "p", it doesn't count the total number of "p"s when there's more than one in a cell. EDIT: Oops sorry, neither does this... Hmmmm - you'd then need to use Split to count the number of p's in each cell
Option Explicit
'
Sub FindPee()
'
Dim Cell As Range, FirstAddress As String, N As Long
'
With Range("D9:D50")
Set Cell = .Find("p", LookIn:=xlValues)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
N = N + 1
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
'
MsgBox N
'
End Sub

mdmackillop
06-16-2006, 12:54 AM
Here's the split pea solution


Sub FindPea()
Dim pod As Range, peas As Long
For Each pod In Range("D9:D50")
If UBound(Split(pod, "p")) <> -1 Then
peas = peas + UBound(Split(pod, "p"))
End If
Next
MsgBox peas
End Sub

Hotpepper
06-16-2006, 01:02 AM
Perhaps for an exact match on lower case p only:

=SUMPRODUCT(LEN(D9:D50)-LEN(SUBSTITUTE(D9:D50,"p","")))

or for either capital or lowercase p:


=SUMPRODUCT(LEN(D9:D50)-LEN(SUBSTITUTE(LOWER(D9:D50),"p","")))

mdmackillop
06-16-2006, 01:28 AM
Ingenious :clap: :clap: :clap:

Must get to know more about SUMPRODUCT.

Shazam
06-16-2006, 06:41 AM
Just notice Hotpepper post.



=SUMPRODUCT((LEN(D9:D50)-LEN(SUBSTITUTE(UPPER(D9:D50),"P",""))

mike31z
06-16-2006, 07:31 AM
Solved with 3 answers.

the =countif(d9:d50,"*"&"p"&"*") provided the best results because it counted number of blocks with a "p" in them and that the answer I needed.

the
=SUMPRODUCT((LEN(D9:D50)-LEN(SUBSTITUTE(UPPER(D9:D50),"P","")) Counted all occurances of "P" in the Blocks not exactly what I needed but close. Also after the =sum formula is entered you must "Ctrl+Shift+Enter" to get it to work. MS KB 213889

I did not try the VB solution because I new at VB.


Thanks for all your help

Mike in Wisconsin