Count one character in a string across a range of cells?
 

I have a column of cells containing strings of numbers and letters. I'm
trying to count the occurrence of individual numbers and letters. What
function should I use?

Count one character in a string across a range of cells?
 

If you have a column of items like and want to count the "a" character:

asdfd
aa
dfa
ffdq
faasf
ccv
aarwwe
a32
tg3q

then use:

=SUM(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"a",""))) to display 9

This is an array formula that must be entered with CNTRL-SHFT-ENTER rathr
than just the ENTER key
--
Gary''s Student - gsnu200832


"Andrea" wrote:

I have a column of cells containing strings of numbers and letters. I'm
trying to count the occurrence of individual numbers and letters. What
function should I use?

Count one character in a string across a range of cells?
 

Hi,

In 2007 you could use

=SUMPRODUCT(LEN(A:A)-LEN(SUBSTITUTE(A:A,"a",)))

or in all versions:

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a",)))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Andrea" wrote:

I have a column of cells containing strings of numbers and letters. I'm
trying to count the occurrence of individual numbers and letters. What
function should I use?

Count one character in a string across a range of cells?
 

Just to point out... this formula is case sensitive... it will not count
upper case A's, only lower case ones. The standard method to do a case
insensitive count would be this...

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER("a"),"")))

an alternative to this would be this...

=SUMPRODUCT(COUNTIF(A1:A10,"*"&REPT("a"&"*",ROW(1: 99))))

provided you know there are no more than a maximum of 99 A's or a's in any
one cell (if there could be more, just increase the 99 to that number or
more).

--
Rick (MVP - Excel)


"Shane Devenshire" wrote in
message ...
Hi,

In 2007 you could use

=SUMPRODUCT(LEN(A:A)-LEN(SUBSTITUTE(A:A,"a",)))

or in all versions:

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"a",)))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Andrea" wrote:

I have a column of cells containing strings of numbers and letters. I'm
trying to count the occurrence of individual numbers and letters. What
function should I use?