counting the first letters or first 2 letters in strings
 

Hi all.

I have some columns with strings of letters and words (the results of a
spelling test) and I want to count up how often each letter of the alphabet
(case-sensitive) is used in the FIRST position of each entry, working with
one column at a time. What combination of functions should I use? Without
having much experience with Excel, I tried this formula for finding
lower-case a's and was given an error message: sum(if((find"",
A2:A160)="a"),1,0)). I was trying to tell the computer to sum each cell over
the range A2:A160 whose first-position character is /a/, letting each
instance of /a/ equal 1.

To complicate matters, a second question: I have to also count up the number
of reversed letters at the /beginning/ of each string. Reversed letters have
been coded as c1, e1, f1, etc. How would I isolate those 2-character
units--again, only at the beginning of the strings--and count them up on a
column-by-column basis?

Much obliged for your assistance!!
--
Jen

counting the first letters or first 2 letters in strings
 

That seems to work; thank you, Rick!
--
Jen


"Rick Rothstein" wrote:

Since you want case sensitivity, try it like this...

=SUMPRODUCT(--EXACT(LEFT(A2:A160,1),"A"))

=SUMPRODUCT(--EXACT(LEFT(A2:A160,2),"cl"))

Note the number after the range (the 1 and 2) correspond to the length of
the text you are searching for (the "A" and the "cl" respectively).

--
Rick (MVP - Excel)


"Jen" wrote in message
...
Hi all.

I have some columns with strings of letters and words (the results of a
spelling test) and I want to count up how often each letter of the
alphabet
(case-sensitive) is used in the FIRST position of each entry, working with
one column at a time. What combination of functions should I use? Without
having much experience with Excel, I tried this formula for finding
lower-case a's and was given an error message: sum(if((find"",
A2:A160)="a"),1,0)). I was trying to tell the computer to sum each cell
over
the range A2:A160 whose first-position character is /a/, letting each
instance of /a/ equal 1.

To complicate matters, a second question: I have to also count up the
number
of reversed letters at the /beginning/ of each string. Reversed letters
have
been coded as c1, e1, f1, etc. How would I isolate those 2-character
units--again, only at the beginning of the strings--and count them up on a
column-by-column basis?

Much obliged for your assistance!!
--
Jen