Counting cells

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hello Group -

I'm trying to count the number of cells that contain the
word "walk".

Walk
Walk this way
Walking in the sand
To walk away
Let's take a walk

Everything I have tried returns either 5 or 6 but the
correct answer is 4.

Can someone show me the way?

Thanks
Bob P.
 
I don't really know but it may have something to do with an old "basic" programming method. Left$(4)="walk"

Probably not much help,but maybe it'll get your thinking cap spinning.


Pat
 
Hi Bob
one way
=SUMPRODUCT((--NOT(ISERROR(FIND(" walk "," " & A1:A99 & " ")))))

HTH
Frank
 
Peo, can that be edited to also count:

"I like to walk."
"He hates to walk!"
"Does she walk?"


Peo Sjoblom said:
One way


=SUMPRODUCT(--(ISNUMBER(SEARCH("Walk ",A1:A5&" "))))

--

Regards,

Peo Sjoblom
Click to expand...
Walk
Walk this way
Walking in the sand
To walk away
Let's take a walk
 
Hi Peo
I would change this to
=SUMPRODUCT(--(ISNUMBER(SEARCH(" Walk "," "&A1:A5&" "))))
otherwise a word like 'catwalk' would be counted

Frank
 
Thanks Peo. I eventually figured a way stringing together
a bunch of countifs. Your solution is much more compact
and I will use it. Thanks again!

Bob P.
 
Hi Dave
IMHO now it's time for regular expressions to do the
substitution/counting

Frank
 
I get 2 using the OP's example and your formula
if you change the case sensitive find to search it will return 4
 
Hi Dave!

You can use:
=SUMPRODUCT((--NOT(ISERROR(FIND("walk"," " & A1:A99 & " ")))))

But this will also count:
The sidewalk is in bad repair
and
swalk (sealed with a loving kiss)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Frank is correct. Thanks Frank!

Bob P.
-----Original Message-----
Hi Peo
I would change this to
=SUMPRODUCT(--(ISNUMBER(SEARCH(" Walk "," "&A1:A5&" "))))
otherwise a word like 'catwalk' would be counted

Frank






.
Click to expand...
 
Peo said:
I get 2 using the OP's example and your formula
if you change the case sensitive find to search it will return 4
Click to expand...
Hi Peo
good point. thanks for pinting this out
Frank
 
Bob said:
Frank is correct. Thanks Frank!

Bob P.
Click to expand...
Hi Bob
I think finally we got it by a combination of both Peo's and my
approach. Though as Dave pointed out this won't count "walk!" or
"walk?"

Frank
 
Ugly but this seems to work

=SUMPRODUCT(--(ISNUMBER(SEARCH(" Walk ","
"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A50,".",""),"?",""),"!","")&" "))))
 
IMHO now it's time for regular expressions to do the
substitution/counting ...
Click to expand...
...

What defines a separate word? No letters either before or after? If so, define
the name WordChars referring to

="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

then if the strings to be tested were in A1:A10, try

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk"," "&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&" ")+4,1),WordChars))
)
 
Ugly but this seems to work

=SUMPRODUCT(--(ISNUMBER(SEARCH(" Walk ","
"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1:A50,".",""),"?",""),"!","")&" "))))
Click to expand...
...

"First walk, then talk."
"That was one of the walk's nicer features."

I bet I could break the nested function call limit.
 
Here's how I got it to work using countif:

=COUNTIF(B1:B22,G1)+COUNTIF(B1:B22,G1&" "&"*")+COUNTIF
(B1:B22,"*"&" "&G1&" "&"*")+COUNTIF(B1:B22,"*"&" "&G1)

But between Frank and Peo, I ended up with a slightly
modified formula that uses a cell reference rather than
the word:

=SUMPRODUCT(--(ISNUMBER(SEARCH
(" "&G1&" "," "&B1:B22&" "))))

Thanks to everyone. This turned into quite an interesting
little thread.

Bob P.
-----Original Message-----
Ugly but this seems to work

=SUMPRODUCT(--(ISNUMBER(SEARCH(" Walk ","
"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
Click to expand...
(A1:A50,".",""),"?",""),"!","")&" "))))
 
Harlan said:
...
..

What defines a separate word? No letters either before or after? If
so, define the name WordChars referring to

="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

then if the strings to be tested were in A1:A10, try

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk"," "&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&"
")+4,1),WordChars)) )
Click to expand...

Hi Harlan
maybe I made a mistake by translating this into the German version but
this formula seems to count 10 - all instances there 'walk' is within
other words. So the range
walk
walking
walker

returns 7 for me?

Frank
 
Back
Top