Using COUNTIF for a word that is joined by another word in same ce

To make it easy, I am trying to count the number of times the name "Bob"
appears in a range of cells. Most of the time, "Bob" is the only name in the
cell, so COUNTIF would work. However, sometimes there will be 2 names in the
same cell, like this: "Joe/Bob." What kind of formula can I use to count the
number of times "Bob" appears in a range of cells, including the times there
are 2 names in the same cell? With the COUNTIF function, it is only counting
the number of times "Bob" appears alone in a cell within the range of cells.

Well,
unfortunately the BEST way is to seperate the names out.. one row for BOB
and one for Joe.. or several name columns...

I can't think of another function off the top of my head that will count
inside a string..
I'd try my hardest to seperate the data into multiple cols/rows, BUT if I
absolutely had to keep them together, I think the only way is to run a loop
for each value using the mid() function in VB..



"James" wrote:

> To make it easy, I am trying to count the number of times the name "Bob"
> appears in a range of cells. Most of the time, "Bob" is the only name in the
> cell, so COUNTIF would work. However, sometimes there will be 2 names in the
> same cell, like this: "Joe/Bob." What kind of formula can I use to count the
> number of times "Bob" appears in a range of cells, including the times there
> are 2 names in the same cell? With the COUNTIF function, it is only counting
> the number of times "Bob" appears alone in a cell within the range of cells.

One way would be to use a helper column with this formula, and count the 1's
in that column.........

=IF(OR(A1="bob",LEFT(A1,3)="bob",RIGHT(A1,3)="bob"),1,"")

Vaya con Dios,
Chuck, CABGx3



"James" <[email protected]> wrote in message
news:[email protected]...
> To make it easy, I am trying to count the number of times the name "Bob"
> appears in a range of cells. Most of the time, "Bob" is the only name in
the
> cell, so COUNTIF would work. However, sometimes there will be 2 names in
the
> same cell, like this: "Joe/Bob." What kind of formula can I use to count
the
> number of times "Bob" appears in a range of cells, including the times
there
> are 2 names in the same cell? With the COUNTIF function, it is only
counting
> the number of times "Bob" appears alone in a cell within the range of
cells.

maybe

=countif(a1:a10,"*bob*")

But if Bob is embedded in another name, it'll get counted.

Jim/Nabob/ralph

(I couldn't think of another name!)

James wrote:
>
> To make it easy, I am trying to count the number of times the name "Bob"
> appears in a range of cells. Most of the time, "Bob" is the only name in the
> cell, so COUNTIF would work. However, sometimes there will be 2 names in the
> same cell, like this: "Joe/Bob." What kind of formula can I use to count the
> number of times "Bob" appears in a range of cells, including the times there
> are 2 names in the same cell? With the COUNTIF function, it is only counting
> the number of times "Bob" appears alone in a cell within the range of cells.

--

Dave Peterson

Very nice Dave.........I love that one but can never remember it
<g>...........by the way, did you see that it only counts bob/bob/bob as
just one "bob"? .......of course I doubt the OP will experience that
combination, but just a curiosity.....it's apparently counting cells that it
finds a bob in rather than the bob's themselves...........

Vaya con Dios,
Chuck, CABGx3




"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> maybe
>
> =countif(a1:a10,"*bob*")
>
> But if Bob is embedded in another name, it'll get counted.
>
> Jim/Nabob/ralph
>
> (I couldn't think of another name!)
>
> James wrote:
> >
> > To make it easy, I am trying to count the number of times the name "Bob"
> > appears in a range of cells. Most of the time, "Bob" is the only name in
the
> > cell, so COUNTIF would work. However, sometimes there will be 2 names in
the
> > same cell, like this: "Joe/Bob." What kind of formula can I use to count
the
> > number of times "Bob" appears in a range of cells, including the times
there
> > are 2 names in the same cell? With the COUNTIF function, it is only
counting
> > the number of times "Bob" appears alone in a cell within the range of
cells.
>
> --
>
> Dave Peterson

Just responded to a similar question in the excel group where the OP had
*multiple* names in a cell.

A2 to A100 contains name data.
Enter name to count in C1.

Try this:
=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)

NOW ... the caveat:
IF you enter "Fred" in C1, this will *also* count:
Freddy
Freddie
Frederico
Fredric
Frederick
BUT, it will *NOT* count:
Alfred
Wilfred

So watch out for the case sensitivity!
--
Regards,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"CLR" <[email protected]> wrote in message
news:[email protected]...
> Very nice Dave.........I love that one but can never remember it
> <g>...........by the way, did you see that it only counts bob/bob/bob as
> just one "bob"? .......of course I doubt the OP will experience that
> combination, but just a curiosity.....it's apparently counting cells that
it
> finds a bob in rather than the bob's themselves...........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > maybe
> >
> > =countif(a1:a10,"*bob*")
> >
> > But if Bob is embedded in another name, it'll get counted.
> >
> > Jim/Nabob/ralph
> >
> > (I couldn't think of another name!)
> >
> > James wrote:
> > >
> > > To make it easy, I am trying to count the number of times the name
"Bob"
> > > appears in a range of cells. Most of the time, "Bob" is the only name
in
> the
> > > cell, so COUNTIF would work. However, sometimes there will be 2 names
in
> the
> > > same cell, like this: "Joe/Bob." What kind of formula can I use to
count
> the
> > > number of times "Bob" appears in a range of cells, including the times
> there
> > > are 2 names in the same cell? With the COUNTIF function, it is only
> counting
> > > the number of times "Bob" appears alone in a cell within the range of
> cells.
> >
> > --
> >
> > Dave Peterson
>
>

Hi,
You could try this approach(it assumes that the name you are looking for,
e.g., Bob, occurs either alone or in two-name combinations separated by a
slash, e.g., Bob/Joe or Joe/John, AND NOT in any othr format).
Let's imagine that the first name is in A2.
Use a helper column (say B) Make sure B1 is empty (or 0)

Enter the following formula in B2 and extend it to the rest of the rows.

=B1+IF(ISNUMBER(FIND("/",A2)),IF(LEFT(A2,FIND("/",A2)-1)="Bob",1,IF(RIGHT(A2,LEN(A2)-FIND("/",A2))="Bob",1,0)),IF(A2="Bob",1,0))

The formula would succesively add the occurrence of "Bob" in any of the
three possible formats; thus, the number at the bottom of column B is what
you want.
(Note: It will exclude variations of 'Bob" and also names where "Bob" is
embedded, but is not case-sensitive - i.e., would count "Bob" and 'bob"....)

Hope it works!
Regards,
B.R. Ramachandran

"James" wrote:

> To make it easy, I am trying to count the number of times the name "Bob"
> appears in a range of cells. Most of the time, "Bob" is the only name in the
> cell, so COUNTIF would work. However, sometimes there will be 2 names in the
> same cell, like this: "Joe/Bob." What kind of formula can I use to count the
> number of times "Bob" appears in a range of cells, including the times there
> are 2 names in the same cell? With the COUNTIF function, it is only counting
> the number of times "Bob" appears alone in a cell within the range of cells.

Well, ok good,.........yours counts bob/bob/bob as 3 alright, but that
case-sensitivity stuff drives me nuts...........
when you get "older", you'll see............<g>

Vaya con Dios,
Chuck, CABGx3




"RagDyer" <[email protected]> wrote in message
news:#[email protected]...
> Just responded to a similar question in the excel group where the OP had
> *multiple* names in a cell.
>
> A2 to A100 contains name data.
> Enter name to count in C1.
>
> Try this:
> =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
>
> NOW ... the caveat:
> IF you enter "Fred" in C1, this will *also* count:
> Freddy
> Freddie
> Frederico
> Fredric
> Frederick
> BUT, it will *NOT* count:
> Alfred
> Wilfred
>
> So watch out for the case sensitivity!
> --
> Regards,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Very nice Dave.........I love that one but can never remember it
> > <g>...........by the way, did you see that it only counts bob/bob/bob as
> > just one "bob"? .......of course I doubt the OP will experience that
> > combination, but just a curiosity.....it's apparently counting cells
that
> it
> > finds a bob in rather than the bob's themselves...........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > maybe
> > >
> > > =countif(a1:a10,"*bob*")
> > >
> > > But if Bob is embedded in another name, it'll get counted.
> > >
> > > Jim/Nabob/ralph
> > >
> > > (I couldn't think of another name!)
> > >
> > > James wrote:
> > > >
> > > > To make it easy, I am trying to count the number of times the name
> "Bob"
> > > > appears in a range of cells. Most of the time, "Bob" is the only
name
> in
> > the
> > > > cell, so COUNTIF would work. However, sometimes there will be 2
names
> in
> > the
> > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> count
> > the
> > > > number of times "Bob" appears in a range of cells, including the
times
> > there
> > > > are 2 names in the same cell? With the COUNTIF function, it is only
> > counting
> > > > the number of times "Bob" appears alone in a cell within the range
of
> > cells.
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >
>

You mean I'll "see" in, what was it, you're 3 months older then me, so I
then automatically become as "nuts" as you?<vbg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Well, ok good,.........yours counts bob/bob/bob as 3 alright, but that
> case-sensitivity stuff drives me nuts...........
> when you get "older", you'll see............<g>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "RagDyer" <[email protected]> wrote in message
> news:#[email protected]...
> > Just responded to a similar question in the excel group where the OP had
> > *multiple* names in a cell.
> >
> > A2 to A100 contains name data.
> > Enter name to count in C1.
> >
> > Try this:
> > =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
> >
> > NOW ... the caveat:
> > IF you enter "Fred" in C1, this will *also* count:
> > Freddy
> > Freddie
> > Frederico
> > Fredric
> > Frederick
> > BUT, it will *NOT* count:
> > Alfred
> > Wilfred
> >
> > So watch out for the case sensitivity!
> > --
> > Regards,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Very nice Dave.........I love that one but can never remember it
> > > <g>...........by the way, did you see that it only counts bob/bob/bob
as
> > > just one "bob"? .......of course I doubt the OP will experience that
> > > combination, but just a curiosity.....it's apparently counting cells
> that
> > it
> > > finds a bob in rather than the bob's themselves...........
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > maybe
> > > >
> > > > =countif(a1:a10,"*bob*")
> > > >
> > > > But if Bob is embedded in another name, it'll get counted.
> > > >
> > > > Jim/Nabob/ralph
> > > >
> > > > (I couldn't think of another name!)
> > > >
> > > > James wrote:
> > > > >
> > > > > To make it easy, I am trying to count the number of times the name
> > "Bob"
> > > > > appears in a range of cells. Most of the time, "Bob" is the only
> name
> > in
> > > the
> > > > > cell, so COUNTIF would work. However, sometimes there will be 2
> names
> > in
> > > the
> > > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> > count
> > > the
> > > > > number of times "Bob" appears in a range of cells, including the
> times
> > > there
> > > > > are 2 names in the same cell? With the COUNTIF function, it is
only
> > > counting
> > > > > the number of times "Bob" appears alone in a cell within the range
> of
> > > cells.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > >
> >
>
>

LOL..........right-on RD, 3 months and counting........ <avbg)

Vaya con Dios,
Chuck, CABGx3


"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> You mean I'll "see" in, what was it, you're 3 months older then me, so I
> then automatically become as "nuts" as you?<vbg>
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Well, ok good,.........yours counts bob/bob/bob as 3 alright, but that
> > case-sensitivity stuff drives me nuts...........
> > when you get "older", you'll see............<g>
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "RagDyer" <[email protected]> wrote in message
> > news:#[email protected]...
> > > Just responded to a similar question in the excel group where the OP
had
> > > *multiple* names in a cell.
> > >
> > > A2 to A100 contains name data.
> > > Enter name to count in C1.
> > >
> > > Try this:
> > > =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
> > >
> > > NOW ... the caveat:
> > > IF you enter "Fred" in C1, this will *also* count:
> > > Freddy
> > > Freddie
> > > Frederico
> > > Fredric
> > > Frederick
> > > BUT, it will *NOT* count:
> > > Alfred
> > > Wilfred
> > >
> > > So watch out for the case sensitivity!
> > > --
> > > Regards,
> > >
> > > RD
> > > ==============================================
> > > Please keep all correspondence within the Group, so all may benefit!
> > > ==============================================
> > >
> > >
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Very nice Dave.........I love that one but can never remember it
> > > > <g>...........by the way, did you see that it only counts
bob/bob/bob
> as
> > > > just one "bob"? .......of course I doubt the OP will experience
that
> > > > combination, but just a curiosity.....it's apparently counting cells
> > that
> > > it
> > > > finds a bob in rather than the bob's themselves...........
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > >
> > > >
> > > > "Dave Peterson" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > maybe
> > > > >
> > > > > =countif(a1:a10,"*bob*")
> > > > >
> > > > > But if Bob is embedded in another name, it'll get counted.
> > > > >
> > > > > Jim/Nabob/ralph
> > > > >
> > > > > (I couldn't think of another name!)
> > > > >
> > > > > James wrote:
> > > > > >
> > > > > > To make it easy, I am trying to count the number of times the
name
> > > "Bob"
> > > > > > appears in a range of cells. Most of the time, "Bob" is the only
> > name
> > > in
> > > > the
> > > > > > cell, so COUNTIF would work. However, sometimes there will be 2
> > names
> > > in
> > > > the
> > > > > > same cell, like this: "Joe/Bob." What kind of formula can I use
to
> > > count
> > > > the
> > > > > > number of times "Bob" appears in a range of cells, including the
> > times
> > > > there
> > > > > > are 2 names in the same cell? With the COUNTIF function, it is
> only
> > > > counting
> > > > > > the number of times "Bob" appears alone in a cell within the
range
> > of
> > > > cells.
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > >
> > > >
> > >
> >
> >
>

But you could use something like:

=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(UPPER(A2:A100),UPPER(C1),"")))/LEN(C1)

So that case isn't a problem.



RagDyer wrote:
>
> Just responded to a similar question in the excel group where the OP had
> *multiple* names in a cell.
>
> A2 to A100 contains name data.
> Enter name to count in C1.
>
> Try this:
> =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
>
> NOW ... the caveat:
> IF you enter "Fred" in C1, this will *also* count:
> Freddy
> Freddie
> Frederico
> Fredric
> Frederick
> BUT, it will *NOT* count:
> Alfred
> Wilfred
>
> So watch out for the case sensitivity!
> --
> Regards,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Very nice Dave.........I love that one but can never remember it
> > <g>...........by the way, did you see that it only counts bob/bob/bob as
> > just one "bob"? .......of course I doubt the OP will experience that
> > combination, but just a curiosity.....it's apparently counting cells that
> it
> > finds a bob in rather than the bob's themselves...........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > maybe
> > >
> > > =countif(a1:a10,"*bob*")
> > >
> > > But if Bob is embedded in another name, it'll get counted.
> > >
> > > Jim/Nabob/ralph
> > >
> > > (I couldn't think of another name!)
> > >
> > > James wrote:
> > > >
> > > > To make it easy, I am trying to count the number of times the name
> "Bob"
> > > > appears in a range of cells. Most of the time, "Bob" is the only name
> in
> > the
> > > > cell, so COUNTIF would work. However, sometimes there will be 2 names
> in
> > the
> > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> count
> > the
> > > > number of times "Bob" appears in a range of cells, including the times
> > there
> > > > are 2 names in the same cell? With the COUNTIF function, it is only
> > counting
> > > > the number of times "Bob" appears alone in a cell within the range of
> > cells.
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >

--

Dave Peterson

I like that.
Good idea!

Plus, it prevents Chuck from becoming "nuts".<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
But you could use something like:

=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(UPPER(A2:A100),UPPER(C1),"")))/LEN(C
1)

So that case isn't a problem.



RagDyer wrote:
>
> Just responded to a similar question in the excel group where the OP had
> *multiple* names in a cell.
>
> A2 to A100 contains name data.
> Enter name to count in C1.
>
> Try this:
> =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
>
> NOW ... the caveat:
> IF you enter "Fred" in C1, this will *also* count:
> Freddy
> Freddie
> Frederico
> Fredric
> Frederick
> BUT, it will *NOT* count:
> Alfred
> Wilfred
>
> So watch out for the case sensitivity!
> --
> Regards,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Very nice Dave.........I love that one but can never remember it
> > <g>...........by the way, did you see that it only counts bob/bob/bob as
> > just one "bob"? .......of course I doubt the OP will experience that
> > combination, but just a curiosity.....it's apparently counting cells
that
> it
> > finds a bob in rather than the bob's themselves...........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > maybe
> > >
> > > =countif(a1:a10,"*bob*")
> > >
> > > But if Bob is embedded in another name, it'll get counted.
> > >
> > > Jim/Nabob/ralph
> > >
> > > (I couldn't think of another name!)
> > >
> > > James wrote:
> > > >
> > > > To make it easy, I am trying to count the number of times the name
> "Bob"
> > > > appears in a range of cells. Most of the time, "Bob" is the only
name
> in
> > the
> > > > cell, so COUNTIF would work. However, sometimes there will be 2
names
> in
> > the
> > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> count
> > the
> > > > number of times "Bob" appears in a range of cells, including the
times
> > there
> > > > are 2 names in the same cell? With the COUNTIF function, it is only
> > counting
> > > > the number of times "Bob" appears alone in a cell within the range
of
> > cells.
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >

--

Dave Peterson

BUT, now have to add to the caveat:

It *also* counts "alfred" & "wilfred"<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"RagDyeR" <[email protected]> wrote in message
news:%[email protected]...
I like that.
Good idea!

Plus, it prevents Chuck from becoming "nuts".<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
But you could use something like:

=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(UPPER(A2:A100),UPPER(C1),"")))/LEN(C
1)

So that case isn't a problem.



RagDyer wrote:
>
> Just responded to a similar question in the excel group where the OP had
> *multiple* names in a cell.
>
> A2 to A100 contains name data.
> Enter name to count in C1.
>
> Try this:
> =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
>
> NOW ... the caveat:
> IF you enter "Fred" in C1, this will *also* count:
> Freddy
> Freddie
> Frederico
> Fredric
> Frederick
> BUT, it will *NOT* count:
> Alfred
> Wilfred
>
> So watch out for the case sensitivity!
> --
> Regards,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Very nice Dave.........I love that one but can never remember it
> > <g>...........by the way, did you see that it only counts bob/bob/bob as
> > just one "bob"? .......of course I doubt the OP will experience that
> > combination, but just a curiosity.....it's apparently counting cells
that
> it
> > finds a bob in rather than the bob's themselves...........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > maybe
> > >
> > > =countif(a1:a10,"*bob*")
> > >
> > > But if Bob is embedded in another name, it'll get counted.
> > >
> > > Jim/Nabob/ralph
> > >
> > > (I couldn't think of another name!)
> > >
> > > James wrote:
> > > >
> > > > To make it easy, I am trying to count the number of times the name
> "Bob"
> > > > appears in a range of cells. Most of the time, "Bob" is the only
name
> in
> > the
> > > > cell, so COUNTIF would work. However, sometimes there will be 2
names
> in
> > the
> > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> count
> > the
> > > > number of times "Bob" appears in a range of cells, including the
times
> > there
> > > > are 2 names in the same cell? With the COUNTIF function, it is only
> > counting
> > > > the number of times "Bob" appears alone in a cell within the range
of
> > cells.
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >

--

Dave Peterson

If the data is nice (separated by commas--no spaces), you could use:

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

(all one cell)

The formula essentially adds a leading and trailing comma to each cell in the
range to count. And it adds the same leading and trailing commas in C1.

So now the formula looks for ",fred,".

You could remove extra spaces with =substitute(a1," ","").

And include that in the formula, too. But for me, I'd insert some extra columns
to do the clean up work. I find breaking it into smaller pieces that I can stop
and verify makes it easier than those giant formulas.



RagDyeR wrote:
>
> BUT, now have to add to the caveat:
>
> It *also* counts "alfred" & "wilfred"<g>
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "RagDyeR" <[email protected]> wrote in message
> news:%[email protected]...
> I like that.
> Good idea!
>
> Plus, it prevents Chuck from becoming "nuts".<g>
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> But you could use something like:
>
> =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(UPPER(A2:A100),UPPER(C1),"")))/LEN(C
> 1)
>
> So that case isn't a problem.
>
> RagDyer wrote:
> >
> > Just responded to a similar question in the excel group where the OP had
> > *multiple* names in a cell.
> >
> > A2 to A100 contains name data.
> > Enter name to count in C1.
> >
> > Try this:
> > =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
> >
> > NOW ... the caveat:
> > IF you enter "Fred" in C1, this will *also* count:
> > Freddy
> > Freddie
> > Frederico
> > Fredric
> > Frederick
> > BUT, it will *NOT* count:
> > Alfred
> > Wilfred
> >
> > So watch out for the case sensitivity!
> > --
> > Regards,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Very nice Dave.........I love that one but can never remember it
> > > <g>...........by the way, did you see that it only counts bob/bob/bob as
> > > just one "bob"? .......of course I doubt the OP will experience that
> > > combination, but just a curiosity.....it's apparently counting cells
> that
> > it
> > > finds a bob in rather than the bob's themselves...........
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > maybe
> > > >
> > > > =countif(a1:a10,"*bob*")
> > > >
> > > > But if Bob is embedded in another name, it'll get counted.
> > > >
> > > > Jim/Nabob/ralph
> > > >
> > > > (I couldn't think of another name!)
> > > >
> > > > James wrote:
> > > > >
> > > > > To make it easy, I am trying to count the number of times the name
> > "Bob"
> > > > > appears in a range of cells. Most of the time, "Bob" is the only
> name
> > in
> > > the
> > > > > cell, so COUNTIF would work. However, sometimes there will be 2
> names
> > in
> > > the
> > > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> > count
> > > the
> > > > > number of times "Bob" appears in a range of cells, including the
> times
> > > there
> > > > > are 2 names in the same cell? With the COUNTIF function, it is only
> > > counting
> > > > > the number of times "Bob" appears alone in a cell within the range
> of
> > > cells.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson

Yup, yup........life is good..............nice one Dave.........

Vaya con Dios,
Chuck, CABGx3



"RagDyeR" <[email protected]> wrote in message
news:#[email protected]...
> I like that.
> Good idea!
>
> Plus, it prevents Chuck from becoming "nuts".<g>
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> But you could use something like:
>
>
=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(UPPER(A2:A100),UPPER(C1),"")))/LEN(C
> 1)
>
> So that case isn't a problem.
>
>
>
> RagDyer wrote:
> >
> > Just responded to a similar question in the excel group where the OP had
> > *multiple* names in a cell.
> >
> > A2 to A100 contains name data.
> > Enter name to count in C1.
> >
> > Try this:
> > =SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)
> >
> > NOW ... the caveat:
> > IF you enter "Fred" in C1, this will *also* count:
> > Freddy
> > Freddie
> > Frederico
> > Fredric
> > Frederick
> > BUT, it will *NOT* count:
> > Alfred
> > Wilfred
> >
> > So watch out for the case sensitivity!
> > --
> > Regards,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Very nice Dave.........I love that one but can never remember it
> > > <g>...........by the way, did you see that it only counts bob/bob/bob
as
> > > just one "bob"? .......of course I doubt the OP will experience that
> > > combination, but just a curiosity.....it's apparently counting cells
> that
> > it
> > > finds a bob in rather than the bob's themselves...........
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > >
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > maybe
> > > >
> > > > =countif(a1:a10,"*bob*")
> > > >
> > > > But if Bob is embedded in another name, it'll get counted.
> > > >
> > > > Jim/Nabob/ralph
> > > >
> > > > (I couldn't think of another name!)
> > > >
> > > > James wrote:
> > > > >
> > > > > To make it easy, I am trying to count the number of times the name
> > "Bob"
> > > > > appears in a range of cells. Most of the time, "Bob" is the only
> name
> > in
> > > the
> > > > > cell, so COUNTIF would work. However, sometimes there will be 2
> names
> > in
> > > the
> > > > > same cell, like this: "Joe/Bob." What kind of formula can I use to
> > count
> > > the
> > > > > number of times "Bob" appears in a range of cells, including the
> times
> > > there
> > > > > are 2 names in the same cell? With the COUNTIF function, it is
only
> > > counting
> > > > > the number of times "Bob" appears alone in a cell within the range
> of
> > > cells.
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > >
>
> --
>
> Dave Peterson
>
>