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

[Guest]

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.

 

[Guest]

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..

 

[CLR]

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

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]

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!)

 

[CLR]

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

 

[RagDyer]

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!
==============================================

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

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!)

in

the

in

the

count

 

[Guest]

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

 

[CLR]

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

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!
==============================================

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

name

in

names

 

[RagDyer]

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>

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[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.

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!
==============================================

 

[RagDyeR]

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 !
--------------------------------------------------------------------

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.

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!
==============================================

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

name

in

names

 

[RagDyeR]

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 !
--------------------------------------------------------------------

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 !
--------------------------------------------------------------------

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.

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!
==============================================

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

name

in

names

 

[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.

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3