Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and summing case-sensitive text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and summing case-sensitive text
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and summing case-sensitive text
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~ -- Jen "Mike H" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and summing case-sensitive text
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and summing case-sensitive text
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and summing case-sensitive text
=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 |
#7
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting - case sensitive text | Excel Worksheet Functions | |||
countif function: how to distinguish case/make case sensitive | Excel Worksheet Functions | |||
how do I get formulas to recognize case sensitive text | Excel Programming | |||
Case Sensitive w/ IF | Excel Worksheet Functions | |||
.Name case sensitive | Excel Programming |