Countif

[gunny1979]

Hi All,

I have the follwing formula that will count the number of times a word appears in a range, it is as follows:

Please Login or Register  to view this content.

How do i get it to search for text within a cell??

Regards

[oldchippy]

Try this,

=COUNTIF(A:A,"*")

[starguy]

Hi All,

I have the follwing formula that will count the number of times a word appears in a range, it is as follows:

Please Login or Register  to view this content.

How do i get it to search for text within a cell??

Regards

if cell A1 contains 122333 and you want to count that how many times 3 appears in cell A1, try this

=LEN(A1)-LEN(SUBSTITUTE(A1,3,""))

hope this is what you asked for.

[gunny1979]

Sorry I dont think I explained myslef clearly I was in a bit of rush.

I meant how do I count the number times a word that is placed in a cell appears in a range.

Thanks

[starguy]

for your information
COUNTIF does not count the characters within a cell.

Regards.

[starguy]

Sorry I dont think I explained myslef clearly I was in a bit of rush.

I meant how do I count the number times a word that is placed in a cell appears in a range.

Thanks

sorry
like this

=COUNTIF(A1:A50,"="&B1)

[gunny1979]

Hi Starguy,

Thanks for your help

Unfortunatley this will only count the number times it appears exactly!

for intance people could type in info, information etc.

the other code

Please Login or Register  to view this content.

Counted the instances of info for me, but Ihad to type info into the formula
is there a way of doing this for a referenced cell, so users can just type the word they are looking for into a cell??

Thanks

[starguy]

Hi Starguy,

Thanks for your help

Unfortunatley this will only count the number times it appears exactly!

for intance people could type in info, information etc.

the other code

Please Login or Register  to view this content.

Counted the instances of info for me, but Ihad to type info into the formula
is there a way of doing this for a referenced cell, so users can just type the word they are looking for into a cell??

Thanks

have you tried formula I posted above
i-e
=COUNTIF(A1:A50,"="&B1)
or
=COUNTIF(A1:A50,B1)

[gunny1979]

Hi Starguy,

I have yes but it only counts the occurences of when it has been typed exactly as it is in the reference cell.

Regards

[oldchippy]

Hi Gunny,

If you have cells A1:A4

A1 = information
A2 = inform
A3 = form
A4 = info

Try this in B1

=COUNTIF(A1:A4,"info*") result 3. Is that what you are after?

[oldchippy]

Duplicate post

http://www.excelforum.com/showthread...=1#post1756957

[daddylonglegs]

=countif(a1:a50,"*"&b1&"*")