Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if "nn"
appears twice in a single cell it is only counted once - not twice. How do I
have "nn" (or anything else) counted for the number of times it appears in a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

With your range in A1:A20 and the search string in cell B1; try the below
formula and feedback. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,B1,"")))/LEN(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Ken Curtis" wrote:

Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if "nn"
appears twice in a single cell it is only counted once - not twice. How do I
have "nn" (or anything else) counted for the number of times it appears in a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Missed to mention that the below formula is case-sensitive..If you want to
override that use the below instead..

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(UPPER(A1:A20),UPPER(B1),"")))/LEN(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With your range in A1:A20 and the search string in cell B1; try the below
formula and feedback. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,B1,"")))/LEN(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Ken Curtis" wrote:

Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if "nn"
appears twice in a single cell it is only counted once - not twice. How do I
have "nn" (or anything else) counted for the number of times it appears in a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Jacob, Thanks for your help. However: Where do I paste your formula?
The data range is D4:T15, and the 'Countif' function (where I want my
result) is in D34. By the way, I need "nn" (or anything else) to be treated
as a word.
--
Ken Curtis


"Jacob Skaria" wrote:

Missed to mention that the below formula is case-sensitive..If you want to
override that use the below instead..

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(UPPER(A1:A20),UPPER(B1),"")))/LEN(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With your range in A1:A20 and the search string in cell B1; try the below
formula and feedback. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,B1,"")))/LEN(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Ken Curtis" wrote:

Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if "nn"
appears twice in a single cell it is only counted once - not twice. How do I
have "nn" (or anything else) counted for the number of times it appears in a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Ken

Few points

1. The formula which you posted do not give a word count but only a string
count; isnt it. but now you are looking for a word count..right?

2. Paste the formula in D34. Again this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

3. There are two versions of the formula; one is case senstive and the other
not. Choose as required.

With the seach word in cell A1 try the below..

To search words
=SUM(LEN(" " & SUBSTITUTE(D4:T15," "," ") & " ")-LEN(SUBSTITUTE(" " &
SUBSTITUTE(UPPER(D4:T15)," "," ") & " "," " & UPPER(A1) & " ","")))/LEN(" "
& A1 & " ")

To search words(case sensitive)
=SUM(LEN(" " & SUBSTITUTE(D4:T15," "," ") & " ")-LEN(SUBSTITUTE(" " &
SUBSTITUTE(D4:T15," "," ") & " "," " & A1 & " ","")))/LEN(" " & A1 & " ")

If this post helps click Yes
---------------
Jacob Skaria


"Ken Curtis" wrote:

Jacob, Thanks for your help. However: Where do I paste your formula?
The data range is D4:T15, and the 'Countif' function (where I want my
result) is in D34. By the way, I need "nn" (or anything else) to be treated
as a word.
--
Ken Curtis


"Jacob Skaria" wrote:

Missed to mention that the below formula is case-sensitive..If you want to
override that use the below instead..

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(UPPER(A1:A20),UPPER(B1),"")))/LEN(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

With your range in A1:A20 and the search string in cell B1; try the below
formula and feedback. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

=SUM(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,B1,"")))/LEN(B1)

If this post helps click Yes
---------------
Jacob Skaria


"Ken Curtis" wrote:

Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if "nn"
appears twice in a single cell it is only counted once - not twice. How do I
have "nn" (or anything else) counted for the number of times it appears in a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

It depends on how "nn" is to be treated. Is "nn" to be considered a "word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice. How
do I
have "nn" (or anything else) counted for the number of times it appears in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a "word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice. How
do I
have "nn" (or anything else) counted for the number of times it appears in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range
of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice.
How
do I
have "nn" (or anything else) counted for the number of times it appears
in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Yes!!
This appears (and works lile) it is exactly what I need! Thanks!
--
Ken Curtis


"T. Valko" wrote:

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range
of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice.
How
do I
have "nn" (or anything else) counted for the number of times it appears
in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

This is going to seem rather odd, 'T.Valko', but your formula (which does
work) only works if there is something between two 'nn's. For example in a
single cell with two 'nn's in it, only one 'nn' is counted unless another
word (for example 'ai') is placed between the two 'nn's. Put another way: nn
is counted as one nn, nn ai nn is counted as two nn's, but nn nn is only
counted as ONE nn NOT two. Suggestion?
--
Ken Curtis


"T. Valko" wrote:

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range
of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice.
How
do I
have "nn" (or anything else) counted for the number of times it appears
in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Your formula works wonderfully ... except for one odd thing: It does not
count two consecutive 'nn's. For example: nn returns "1", nn ai nn returns
"2", but nn nn only returns 1. Am I missing something?


--
Ken Curtis


"T. Valko" wrote:

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range
of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice.
How
do I
have "nn" (or anything else) counted for the number of times it appears
in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Please ignore my double entry. When I posted the forst one a 'box' popped up
to say that the service was 'temporarly unavailable', so I thought the
message had not been sent.
--
Ken Curtis


"Ken Curtis" wrote:

Your formula works wonderfully ... except for one odd thing: It does not
count two consecutive 'nn's. For example: nn returns "1", nn ai nn returns
"2", but nn nn only returns 1. Am I missing something?


--
Ken Curtis


"T. Valko" wrote:

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range
of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice.
How
do I
have "nn" (or anything else) counted for the number of times it appears
in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Yes, this works ... sort of. Here's the odd problem: 'nn' returns 1 , 'nn
ai nn' returns 2, however 'nn nn' returns only 1 (not 2, as it should).
Suggestions?
--
Ken Curtis


"T. Valko" wrote:

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range
of
cells and tells me how many times "nn" appears. It's fine. However, if
"nn"
appears twice in a single cell it is only counted once - not twice.
How
do I
have "nn" (or anything else) counted for the number of times it appears
in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Give this variation of Biff's formula a try...

=SUMPRODUCT(LEN(SUBSTITUTE(" "&A1:A10&" "," "&C1&" "," "&C1&" "))-
LEN(SUBSTITUTE(UPPER((SUBSTITUTE(" "&A1:A10&" "," "&C1&" "," "&
C1&" "))),UPPER(" "&C1&" "),"")))/(LEN(C1)+2)

--
Rick (MVP - Excel)


"Ken Curtis" wrote in message
...
Yes, this works ... sort of. Here's the odd problem: 'nn' returns 1 ,
'nn
ai nn' returns 2, however 'nn nn' returns only 1 (not 2, as it should).
Suggestions?
--
Ken Curtis


"T. Valko" wrote:

"nn" is a 'word'

OK, that means "nn" should have spaces on either side of it if it's
within
the string.

nn is the nn
it's nn time!
no time for nn

Based on those examples this formula returns 4:

All on one line

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" "," NN ","")))/4

If you want to use a cell to hold the criteria:

C1 = nn

=SUMPRODUCT(LEN(" "&A1:A3&" ")-LEN
(SUBSTITUTE(" "&UPPER(A1:A3)
&" ",UPPER(" "&C1&" "),"")))/(LEN(C1)+2)


--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
T.Valko

"nn" is a 'word'
--
Ken Curtis


"T. Valko" wrote:

It depends on how "nn" is to be treated. Is "nn" to be considered a
"word"
by itself or can it be part of any word?

For the best possible solution it would be a good idea if you posted
some
samples of your data and the expected result.

--
Biff
Microsoft Excel MVP


"Ken Curtis" wrote in message
...
Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a
range
of
cells and tells me how many times "nn" appears. It's fine. However,
if
"nn"
appears twice in a single cell it is only counted once - not twice.
How
do I
have "nn" (or anything else) counted for the number of times it
appears
in
a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis

Word match in a range....

To search words
=SUM(LEN(" " & SUBSTITUTE(A1:A20," "," ") & " ")-LEN(SUBSTITUTE(" " &
SUBSTITUTE(UPPER(A1:A20)," "," ") & " "," " & UPPER(B1) & " ","")))/LEN(" "
& B1& " ")

To search words(case sensitive)
=SUM(LEN(" " & SUBSTITUTE(A1:A20," "," ") & " ")-LEN(SUBSTITUTE(" " &
SUBSTITUTE(A1:A20," "," ") & " "," " & B1 & " ","")))/LEN(" " & B1& " ")


If this post helps click Yes
---------------
Jacob Skaria


"Ken Curtis" wrote:

Hi,
I am using a function (=COUNTIF(data,"*"&nn&"*") that searches a range of
cells and tells me how many times "nn" appears. It's fine. However, if "nn"
appears twice in a single cell it is only counted once - not twice. How do I
have "nn" (or anything else) counted for the number of times it appears in a
range including it appearing twice (or more times) in a single cell?
--
Ken Curtis