Posted to microsoft.public.excel.programming
|
|
finding and summing case-sensitive text
Thank you--I think that solves it!
--
Jen
"Dave Peterson" wrote:
=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
|