Counting cells

  • Thread starter Thread starter Bob
  • Start date Start date
Harlan said:
...

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

I bet I could break the nested function call limit.
Click to expand...

lol thats not fair ;-)
Frank
 
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
to take blank rows into account I would amend this excellent solution
by
=SUMPRODUCT(ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk","
"&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&"
Click to expand...
")+4,1),WordChars))*(A1:A10<>""))

Frank
 
...
...
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?
Click to expand...

My test data in A1:A10.

walk to me
walker, talker
to walk a long mile
when walking in Summer
crosswalk or middle of the block
crosswalks or dividing lines
can I walk
where's the crosswalk
how many crosswalks
talk-walk-talk

My formula (entered in cell F12, but that should be immaterial) is

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

My result is 4. With your 3 words, walk, walking and walker each entered in its
own cell in A18:A20, the formula

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A18:A20,SEARCH("?walk"," "&A18:A20),1),WordChars))
*ISERROR(FIND(MID(A18:A20&" ",SEARCH("walk?",A18:A20&" ")+4,1),WordChars))
)

returns 1 on my machine (U.S. English version XL97 SR-2 under NT4 SP-6). What's
your *EXACT* formula?
 
My test data in A1:A10.
walk to me
walker, talker
to walk a long mile
when walking in Summer
crosswalk or middle of the block
crosswalks or dividing lines
can I walk
where's the crosswalk
how many crosswalks
talk-walk-talk

My formula (entered in cell F12, but that should be immaterial) is

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

My result is 4. With your 3 words, walk, walking and walker each
entered in its own cell in A18:A20, the formula

=SUMPRODUCT(
ISERROR(FIND(MID(" "&A18:A20,SEARCH("?walk","
"&A18:A20),1),WordChars)) *ISERROR(FIND(MID(A18:A20&"
",SEARCH("walk?",A18:A20&" ")+4,1),WordChars)) )

returns 1 on my machine (U.S. English version XL97 SR-2 under NT4
SP-6). What's your *EXACT* formula?
Click to expand...

Hi Harlan
error on my side due to my tesdata (some rows were blanks). So your
formula works excellent unless there are blanks in between. They are
counted as matches

Frank
 
Frank Kabel said:
....
error on my side due to my tesdata (some rows were blanks). So your
formula works excellent unless there are blanks in between. They are
counted as matches
Click to expand...

Not just blanks. It'll fail on any cell not containing the substring walk.

=SUMPRODUCT(
ISNUMBER(SEARCH("walk",A1:A10))
*ISERROR(FIND(MID(" "&A1:A10,SEARCH("?walk"," "&A1:A10),1),WordChars))
*ISERROR(FIND(MID(A1:A10&" ",SEARCH("walk?",A1:A10&" ")+4,1),WordChars))
)
 
Back
Top