problem about count text

[Kent]

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]

Try it this way:

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

Hope this helps.

Pete

 

[Kent]

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" <[email protected]>
?¤J®ø®§·s?:[email protected]...
Try it this way:

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

Hope this helps.

Pete

 

[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

 

[kent]

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" <[email protected]>
???????:1f5ac959-c50d-4dd1-994b-732c0edb3980@c34g2000yqn.googlegroups.com...
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

 

[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

 

[kent]

Dear Pete

Again You help me solving it.

thank you
Kent


"Pete_UK" <[email protected]>
???????:02b47564-6549-421a-a94b-526dd97399fe@s31g2000yqs.googlegroups.com...
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

 

[Pete_UK]

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

Pete