finding and summing case-sensitive text

[Jen]

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!

 

[Mike H]

Jen,

I don't understand, does every thing score 1 as in you formula snippet? If
so why does case matter?

If everything does score 1 then try this array formula

=COUNT(IF(A1:A20={"A","B","B1"},1))

add more conditions to meet your requirements.

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[Jen]

Dear Mike- thanks for the swift reply. Actually, case does matter--I should
have been clearer. I have a cell to enter a formula dealing with the a's in
the sample of text, another cell to enter a formula dealing with "a1",
another for "A", another for "b", "B", "B1", etc, up to z. (Why am I doing
this? It's for a statistical analysis of young spellers' letter choice
frequencies.) Does that make things clearer?? Thanks again~

 

[Harald Staff]

Hi Jen

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

HTH. Best wishes Harald

 

[Jen]

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!

 

[Dave Peterson]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Jen]

Thank you--I think that solves it!
--
Jen

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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