=sumproduct(--(isnumber(find("A",m1:m20))))

will count the number of cells with an uppercase A in them (anywhere).

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Jen wrote:

Dear All--

Harald, your suggestion works perfectly for stand-alone A and a, but not for
finding the a's in entries like bAck and ba, where the letter is embedded
within a word, because the EXACT function doesn't use wildcard symbols--which
I need to use to find the A in "bAck," etc. Here is a short list of the
entries I need to search through and from which I need to list the individual
letters, to give you a better idea of the situation: "bAck, qAc1 (the one
stands for a reversal of the previous letter), dak, b, p ,ba, bac, Bc" (each
entry takes up one cell in a column). I need to find ways to isolate each
letter and each "x1" combination.
Any other ideas, anyone? Much obliged!
--
Jen

"Harald Staff" wrote:

Hi Jen

Try
=SUMPRODUCT(--EXACT(M1:M200;"a"))
=SUMPRODUCT(--EXACT(M1:M200;"A"))
and so on

HTH. Best wishes Harald

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

I need to create some formulas that count the number of a given,
case-sensitive letter within a long column. To be specific, I will need
formulas for finding the numbers of "a", "a1" (the 1 is coding for a
reversal), "A", "b", "B", "B1", etc. I wrote
(if(find("A",m1),1,0)+if(find("A",m2),1,0))+... but the FIND function,
which
returns that pesky #VALUE! instead of a 0 for each cell in which the given
letter does not occur, messes up the summing process. Anyone have any
suggestions? Much obliged!
--
Jen



--

Dave Peterson