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

 

[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

 

[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.

 

[Alan Beban]

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

Alan Beban

 

[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