ExcelBanter - problem about count text

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - problem about count text (https://www.excelbanter.com/excel-worksheet-functions/250427-problem-about-count-text.html)

Kent

problem about count text

Dear all,

I have a table

A B
SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A, in this
case, is 3. The formula is =countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent

Pete_UK

problem about count text

Try it this way:

=countif(A2:A5, "*"&B1&"*")

Hope this helps.

Pete

On Dec 8, 9:05*am, "Kent" wrote:
Dear all,

I have a table

A * * * * * * * * * * *B
* * * * * * * * * * * * SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A, in this
case, is 3. The formula is *=countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent

Kent

problem about count text

Thank you Pete, it works.
Another case.

A B C D
E
Jose John
Roy
Game AJoseq Game
Toy BJohnRoy Toy
Toy

If Column B contain text from C1 to E1, then the content at Column A would
indicate in C2 to E3.
What is the formula please.

Kent

"Pete_UK"
...
Try it this way:

=countif(A2:A5, "*"&B1&"*")

Hope this helps.

Pete

On Dec 8, 9:05*am, "Kent" wrote:
Dear all,

I have a table

A ??????????*B
????????????SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A, in
this
case, is 3. The formula is ?countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent

Pete_UK

problem about count text

Glad to hear that the first suggestion worked for you.

I'm sorry, but your second request doesn't make any sense to me. Can
you rephrase it, and repost the example so that it doesn't wrap onto
new lines each time.

Pete

On Dec 8, 11:39*am, "Kent" wrote:
Thank you Pete, it works.
Another case.

A * * * * * * * * * *B * * * * * * * * * *C * * * * * * * * * *D
E
* * * * * * * * * * * * * * * * * * * * * * Jose * * * * * * * *John
Roy
Game * * * * * AJoseq * * * * * *Game
Toy * * * * * * *BJohnRoy * * * * * * * * * * * * * * * *Toy
Toy

If Column B contain text from C1 to E1, then the content at Column A would
indicate in C2 to E3.
What is the formula please.

Kent

"Pete_UK"
...
Try it this way:

=countif(A2:A5, "*"&B1&"*")

Hope this helps.

Pete

On Dec 8, 9:05*am, "Kent" wrote:

Dear all,

I have a table

A ??????????*B
????????????SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A, in
this
case, is 3. The formula is ?countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent- Hide quoted text -

- Show quoted text -

kent[_2_]

problem about count text

A B C D E
Jose John Roy
Game AJoseq Game
Toy BJohnRoy Toy Toy

If Column B contain text from C1 to E1, then the content at Column A would
appear in C2 to E3.
What is the formula please.

Hope you understand my question this time.

"Pete_UK"
...
Glad to hear that the first suggestion worked for you.

I'm sorry, but your second request doesn't make any sense to me. Can
you rephrase it, and repost the example so that it doesn't wrap onto
new lines each time.

Pete

On Dec 8, 11:39 am, "Kent" wrote:
Thank you Pete, it works.
Another case.

A B C D
E
Jose John
Roy
Game AJoseq Game
Toy BJohnRoy Toy
Toy

If Column B contain text from C1 to E1, then the content at Column A would
indicate in C2 to E3.
What is the formula please.

Kent

"Pete_UK"
...
Try it this way:

=countif(A2:A5, "*"&B1&"*")

Hope this helps.

Pete

On Dec 8, 9:05 am, "Kent" wrote:

Dear all,

I have a table

A ?????????? B
????????????SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A, in
this
case, is 3. The formula is ?countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent- Hide quoted text -

- Show quoted text -

Pete_UK

problem about count text

Put this formula in C2:

=IF(ISNUMBER(SEARCH(C$1,$B2)),$A2,"")

Copy it across to E2, then copy C2:E2 down as far as you need.

Hope this helps.

Pete

On Dec 8, 4:01*pm, "kent" wrote:
A * * * * * * * B * * * * * * * C * * * * * * * D * * * * * * * E
* * * * * * * * * * * * * * * * * *Jose * * * * *John * * * * * Roy
Game * *AJoseq * * * * Game
Toy * * BJohnRoy * * * * * * * * * * * * *Toy * * * * * *Toy

If Column B contain text from C1 to E1, then the content at Column A would
appear in C2 to E3.
What is the formula please.

Hope you understand my question this time.

"Pete_UK"
....
Glad to hear that the first suggestion worked for you.

I'm sorry, but your second request doesn't make any sense to me. Can
you rephrase it, and repost the example so that it doesn't wrap onto
new lines each time.

Pete

On Dec 8, 11:39 am, "Kent" wrote:

Thank you Pete, it works.
Another case.

A B C D
E
Jose John
Roy
Game AJoseq Game
Toy BJohnRoy Toy
Toy

If Column B contain text from C1 to E1, then the content at Column A would
indicate in C2 to E3.
What is the formula please.

Kent

"Pete_UK"
? J ....
Try it this way:

=countif(A2:A5, "*"&B1&"*")

Hope this helps.

Pete

On Dec 8, 9:05 am, "Kent" wrote:

Dear all,

I have a table

A ?????????? B
????????????SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A, in
this
case, is 3. The formula is ?countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

kent[_2_]

problem about count text

Dear Pete

Again You help me solving it.

thank you
Kent

"Pete_UK"
...
Put this formula in C2:

=IF(ISNUMBER(SEARCH(C$1,$B2)),$A2,"")

Copy it across to E2, then copy C2:E2 down as far as you need.

Hope this helps.

Pete

On Dec 8, 4:01 pm, "kent" wrote:
A B C D E
Jose John Roy
Game AJoseq Game
Toy BJohnRoy Toy Toy

If Column B contain text from C1 to E1, then the content at Column A would
appear in C2 to E3.
What is the formula please.

Hope you understand my question this time.

"Pete_UK"
...
Glad to hear that the first suggestion worked for you.

I'm sorry, but your second request doesn't make any sense to me. Can
you rephrase it, and repost the example so that it doesn't wrap onto
new lines each time.

Pete

On Dec 8, 11:39 am, "Kent" wrote:

Thank you Pete, it works.
Another case.

A B C D
E
Jose John
Roy
Game AJoseq Game
Toy BJohnRoy Toy
Toy

If Column B contain text from C1 to E1, then the content at Column A
would
indicate in C2 to E3.
What is the formula please.

Kent

"Pete_UK"
? J
...
Try it this way:

=countif(A2:A5, "*"&B1&"*")

Hope this helps.

Pete

On Dec 8, 9:05 am, "Kent" wrote:

Dear all,

I have a table

A ?????????? B
????????????SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A,
in
this
case, is 3. The formula is ?countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried
the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Pete_UK

problem about count text

You're welcome, Kent - thanks for feeding back.

Pete

On Dec 8, 4:42*pm, "kent" wrote:
Dear Pete

Again You help me solving it.

thank you
Kent

"Pete_UK"
....
Put this formula in C2:

=IF(ISNUMBER(SEARCH(C$1,$B2)),$A2,"")

Copy it across to E2, then copy C2:E2 down as far as you need.

Hope this helps.

Pete

On Dec 8, 4:01 pm, "kent" wrote:

A B C D E
Jose John Roy
Game AJoseq Game
Toy BJohnRoy Toy Toy

If Column B contain text from C1 to E1, then the content at Column A would
appear in C2 to E3.
What is the formula please.

Hope you understand my question this time.

"Pete_UK"
...
Glad to hear that the first suggestion worked for you.

I'm sorry, but your second request doesn't make any sense to me. Can
you rephrase it, and repost the example so that it doesn't wrap onto
new lines each time.

Pete

On Dec 8, 11:39 am, "Kent" wrote:

Thank you Pete, it works.
Another case.

A B C D
E
Jose John
Roy
Game AJoseq Game
Toy BJohnRoy Toy
Toy

If Column B contain text from C1 to E1, then the content at Column A
would
indicate in C2 to E3.
What is the formula please.

Kent

"Pete_UK"
? J
....
Try it this way:

=countif(A2:A5, "*"&B1&"*")

Hope this helps.

Pete

On Dec 8, 9:05 am, "Kent" wrote:

Dear all,

I have a table

A ?????????? B
????????????SD
ASDFG
SDFTY
WREGD
UJHDSD

I want to count the no. of appearance of the text at B1 in column A,
in
this
case, is 3. The formula is ?countif(A2:A5, "*SD*").

However as the text "SD" would be changed all the time, I have tried
the
formula =countif(A2:A5, "*(B1)*"), but it fails.

What should the formula please.

Kent- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

All times are GMT +1. The time now is 10:40 PM.