Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT unique letter in a column
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT unique letter in a column
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT unique letter in a column
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT unique letter in a column
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT unique letter in a column
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT unique letter in a column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count unique values in one column for a set value in another col. | Excel Worksheet Functions | |||
Formular to count occurence of a number or letter in a column | Excel Worksheet Functions | |||
Count letter"B" in one column based on unique value among duplicat | Excel Worksheet Functions | |||
Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
count of unique values within a column | Excel Discussion (Misc queries) |