COUNT unique letter in a column

Howdy All,

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

Any help?

THanks,
Brian

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

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" wrote in message
...
Howdy All,

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

Any help?

THanks,
Brian

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...

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

Thanks Rick.
You read my mind, the letters will only appear in a given cell once.

Brian
"Rick Rothstein" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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