Prev: Another Sumif problem
Next: Method of viewing/naming columns or conversions for same
From: Brian on 8 May 2010 11:38
Howdy All,

I want to count the number of occurrences of a partipular letter in a column

Any help?

THanks,
Brian


From: Dave Peterson on 8 May 2010 11:52
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
From: Bernard Liengme on 8 May 2010 13:33
In case Dave has misread your question and the cells have only one letter
each:
=COUNTIF(A1A:100,"A")
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Brian" <wolfmanx(a)charter.net> wrote in message
news:eJhbWSs7KHA.4508(a)TK2MSFTNGP06.phx.gbl...
> Howdy All,
>
> I want to count the number of occurrences of a partipular letter in a
> column
>
> Any help?
>
> THanks,
> Brian
>
From: Brian on 8 May 2010 13:48
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


From: Rick Rothstein on 8 May 2010 14:49
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
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Another Sumif problem
Next: Method of viewing/naming columns or conversions for same