problem about count text

K

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
 
K

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
 
P

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
 
K

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
 
P

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top