Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count specific letters in range of cells?
I am looking for a formula to count the number of R's in a range.
=countif(a1:a3,"*R*")... is only giving me 2 - one for each cell there is an "R"... not 5 for the total number of R's. I need a formula to give me that "total" - 5 number. Any ideas???? A1 = BETH PAGEL (zero R's) A2 = BARRY GUNTHER (3 R's) A3 = RANDY ROST (2 R's) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count specific letters in range of cells?
I am looking for a formula to count the number of R's in a range.
=countif(a1:a3,"*R*")... is only giving me 2 - one for each cell there is an "R"... not 5 for the total number of R's. I need a formula to give me that "total" - 5 number. Any ideas???? A1 = BETH PAGEL (zero R's) A2 = BARRY GUNTHER (3 R's) A3 = RANDY ROST (2 R's) Off the top of my head, this formula should work... =SUMPRODUCT(LEN((A1:A3))-LEN(SUBSTITUTE((A1:A3),"R",""))) Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count specific letters in range of cells?
I am looking for a formula to count the number of R's in a range.
=countif(a1:a3,"*R*")... is only giving me 2 - one for each cell there is an "R"... not 5 for the total number of R's. I need a formula to give me that "total" - 5 number. Any ideas???? A1 = BETH PAGEL (zero R's) A2 = BARRY GUNTHER (3 R's) A3 = RANDY ROST (2 R's) Off the top of my head, this formula should work... =SUMPRODUCT(LEN((A1:A3))-LEN(SUBSTITUTE((A1:A3),"R",""))) By the way, the above is case-sensitive. If you need a case insensitive formula, try this one... =SUMPRODUCT(LEN((A1:A3))-LEN(SUBSTITUTE((A1:A3),{"r","R"},""))) Rick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count specific letters in range of cells?
Rick that worked perfectly! Thank you. Renee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count specific text that occurs in a range of cells | Excel Discussion (Misc queries) | |||
Count Specific word in specific range | Excel Worksheet Functions | |||
Count If Specific word in specific range | Excel Discussion (Misc queries) | |||
formula to count cells containing #'s in a specific range | Excel Worksheet Functions | |||
SPECIFIC WORD COUNT FROM A RANGE | Excel Discussion (Misc queries) |