counting number of special character #

[james‏]

Could any one tell me how to count number of special
character # in a range of cells.

I have used the formular =SUM(LEN(range)-LEN(SUBSTITUTE
(range,"#",""))). The formular does not work with the
character #.

Could any one help.

james

[Alan Beban‏]

=COUNTIF(range,"#")

Alan Beban

[james‏]

thanks aln,
the # is first character of a string. using =countif
(range,"#"), i got zero in return.

james

>.
>

[Bob Phillips‏]

James.

If it is # with other chars in the cell then try
=SUM(LEN(range)-LEN(SUBSTITUTE(range,"#","")))
which is an array formula, so enter with Ctrl-Shift-Enter

--

HTH

Bob Phillips

"james" <jj...@sleh.com> wrote in message
news:096801c36e5a$5805c130$a001...@phx.gbl...

[JAMES‏]

Hello Alan,

The # is at the beginning of a string. I got zero in
return using countif(range,"#"). Do you have any other
solution.

Thanks again.

James

>.
>

[James‏]

Hello Bob,

I used your method but I got message of "#Value!".

James

>.
>

[Tom Ogilvy‏]

=SUM(LEN(H16:H19)-LEN(SUBSTITUTE(H16:H19,"#","")))

worked fine for me if I entered it with Ctrl+Shift+Enter rather than just
enter.

or

=SUMPRODUCT(LEN(H16:H19)-LEN(SUBSTITUTE(H16:H19,"#","")))

entered normally.

--
Regards,
Tom Ogilvy

"james" <jj...@sleh.com> wrote in message

news:033501c36e54$871626f0$a101...@phx.gbl...

[Alan Beban‏]

Sure. =COUNTIF(range,"*#*").
It counts the number of cells with one or more #'s in them.

Alan Beban

[Alan Beban‏]

There seems to be some confusion (at least on my part) as to whether
your cells have more than one # in them, and if they do, what you want
to count.

Alan Beban

[james‏]

hello alan,

thanks for your help. the formular =countif(range,"*#*")
works great. i do have two strings in one cell with each
with a # character.

thanks again.

james

>.
>

[Alan Beban‏]

Thanks for the feedback, James. The formula I provided will count only
the number of cells with one or more #; so if a cell has two #'s, it
will count them as 1. Tom Ogilvy has provided a formula that will count
the number of #'s.

Alan Beban