Counting

K

kelljeff

I am trying to count the number of times a particular phrase appears in
a cell.

example: "#Call" is what I want to count.

however all the cells are formatted like #Callme-name,

Can any one help...
 
D

Domenic

Try...

=COUNTIF(A2:A100,"#Call*")

or

=COUNTIF(A2:A100,B2&"*")

....where B2 contains your criteria, such as '#Call'.

Hope this helps!
 
K

kelljeff

The First worked... Thank you.

Just out of curiosity, I tried that minus the "*" and it did not work.
Does the "*" do something special ?

Again Thank you..
 
D

Domenic

The "*" is a wildcard character. So basically any cell where the first
5 characters are '#Call' gets counted. If you change the formula to...

=COUNTIF(A2:A100,"*#Call*")

....a cell gets counted when "#Call" occurs anywhere in the string. For
example, cells containing the following strings all get counted...

#Call

#Callxxxxx

xxxx#Call

xxxx#Callxxxx

Hope this helps!
 
R

Rookie_User

I used part of this and it got me thinking of how could I count how many
times a character is appears within a CELL. I would like to count the
backward slash character "\". If cell A2 contains "w:\jason\hopson\file.xls"
the number would be 3. Any advise?
 
R

Rookie_User

Domenic,
It worked perfectly - thank you so much. I just wanted to post a
success/closure. However, if you have time, could you better explain why it
works?
 
P

Peo Sjoblom

Lets say there are 12 characters in A2 where of 2 are \

LEN(A2)

will count all characters in A2,

now you substitute the 2 \ with 2 "" meaning that the total count would go
from 12 to 10 ("" is counted as empty using LEN)

thus

LEN(SUBSTITUTE(A2,"\",""))

first substitute then count will give 10 then finally subtract that from the
total count gives

=12-10

gives 2 \


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
Top