COUNT unique letter in a column
in [Functions]
|
From: Dave Peterson on 8 May 2010 15:43 If B1:B2 contained: ppmmee ppmmee Would the count of p's be two? If yes, use Rick's suggestion. His suggestion counts the number of cells with at least one p: =countif(b:b,"*p*") If the the count would be 0 (since there is no exact match), then use Bernard's suggestion. His suggestion looks for a single character in the cell: =countif(b:b,"p") If the count would be 4 (two in B1 + two in B2), then use one of the suggestions I gave. It counts the number of times that character appears in the range. Brian wrote: > Thanks for the input. > The column may actually contain any number of letters. Examples B1 = pme, B2 > = e, B3 = pe, etc. > I want to have a few cells that count the p's, m's and e's. > > Thanks again, > Brian > > "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > news:%23tBuKas7KHA.1316(a)TK2MSFTNGP02.phx.gbl... > >>This will give you the number of lower case a's in a range: >>=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"a","")))/len("a") >> >>If you want to ignore case, you can use: >>=SUMPRODUCT(LEN(A1:A100) >> -LEN(SUBSTITUTE(upper(A1:A100),upper("a"),"")))/len("a") >> >>(Substitute is case-sensitive) >> >>Adjust the ranges to match--but you can't use whole columns (except in >>xl2007+). >> >> >> >> >>Brian wrote: >> >> >>>Howdy All, >>> >>>I want to count the number of occurrences of a partipular letter in a >>>column >>> >>>Any help? >>> >>>THanks, >>>Brian >> >>-- >> >>Dave Peterson > > > -- Dave Peterson
From: Brian on 8 May 2010 17:46 Thanks Rick. You read my mind, the letters will only appear in a given cell once. Brian "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:O%23L3r9t7KHA.3964(a)TK2MSFTNGP05.phx.gbl... > Your use of the word "unique" may be a little confusing. From what I can > see, you want to count the cells with a particular letter in it, even if > that letter is combined with other letters. Also from your *small* example > pool, it appears that the letter won't be repeated within any single cell. > If that is the case... or, if it does repeat in a cell, but you only want > to count that cell once, give this formula a try... > > =COUNTIF(A1:A100,"*p*") > > Adjust the range accordingly and change the letter as needed. > > -- > Rick (MVP - Excel) > > > > "Brian" <wolfmanx(a)charter.net> wrote in message > news:uTh51at7KHA.3964(a)TK2MSFTNGP05.phx.gbl... >> Thanks for the input. >> The column may actually contain any number of letters. Examples B1 = pme, >> B2 = e, B3 = pe, etc. >> I want to have a few cells that count the p's, m's and e's. >> >> Thanks again, >> Brian >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >> news:%23tBuKas7KHA.1316(a)TK2MSFTNGP02.phx.gbl... >>> This will give you the number of lower case a's in a range: >>> =SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"a","")))/len("a") >>> >>> If you want to ignore case, you can use: >>> =SUMPRODUCT(LEN(A1:A100) >>> -LEN(SUBSTITUTE(upper(A1:A100),upper("a"),"")))/len("a") >>> >>> (Substitute is case-sensitive) >>> >>> Adjust the ranges to match--but you can't use whole columns (except in >>> xl2007+). >>> >>> >>> >>> >>> Brian wrote: >>> >>>> Howdy All, >>>> >>>> I want to count the number of occurrences of a partipular letter in a >>>> column >>>> >>>> Any help? >>>> >>>> THanks, >>>> Brian >>> >>> -- >>> >>> Dave Peterson >> >>
First
|
Prev
|
Pages: 1 2 Prev: Another Sumif problem Next: Method of viewing/naming columns or conversions for same |