Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting specific letters in multiple cells
Hi,
Lets say I want to look for the letter "O" in a cell with the text "How are you?" and count the number of "O" in that particular cell, the formula would be something like: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O","")) and the result for "How are you?" will return 2. My question is, what if I want to do it for multiple cells? For instance, from Cell A1 to A10. Thanks in advance. -- Shawn Toh (tohlz) Microsoft MVP PowerPoint (Amazing PowerPoint animations, artworks, games here) http://pptheaven.mvps.org PowerPoint Heaven - The Power to Animate |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting specific letters in multiple cells
Hi,
you could use an array formula. Use CTRL+SHIFT+ENTER to commit the formula rather than the usual ENTER. =SUM(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O",""))) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "tohlz" <pptheaven[AT]gmail[DOT]com wrote in message ... Hi, Lets say I want to look for the letter "O" in a cell with the text "How are you?" and count the number of "O" in that particular cell, the formula would be something like: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O","")) and the result for "How are you?" will return 2. My question is, what if I want to do it for multiple cells? For instance, from Cell A1 to A10. Thanks in advance. -- Shawn Toh (tohlz) Microsoft MVP PowerPoint (Amazing PowerPoint animations, artworks, games here) http://pptheaven.mvps.org PowerPoint Heaven - The Power to Animate |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting specific letters in multiple cells
Try
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O",""))) Mike "tohlz" wrote: Hi, Lets say I want to look for the letter "O" in a cell with the text "How are you?" and count the number of "O" in that particular cell, the formula would be something like: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O","")) and the result for "How are you?" will return 2. My question is, what if I want to do it for multiple cells? For instance, from Cell A1 to A10. Thanks in advance. -- Shawn Toh (tohlz) Microsoft MVP PowerPoint (Amazing PowerPoint animations, artworks, games here) http://pptheaven.mvps.org PowerPoint Heaven - The Power to Animate |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting specific letters in multiple cells
Thanks for the help, Andy and Mike.
-- Shawn Toh (tohlz) Microsoft MVP PowerPoint (Amazing PowerPoint animations, artworks, games here) http://pptheaven.mvps.org PowerPoint Heaven - The Power to Animate "Mike H" wrote: Try =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O",""))) Mike "tohlz" wrote: Hi, Lets say I want to look for the letter "O" in a cell with the text "How are you?" and count the number of "O" in that particular cell, the formula would be something like: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O","")) and the result for "How are you?" will return 2. My question is, what if I want to do it for multiple cells? For instance, from Cell A1 to A10. Thanks in advance. -- Shawn Toh (tohlz) Microsoft MVP PowerPoint (Amazing PowerPoint animations, artworks, games here) http://pptheaven.mvps.org PowerPoint Heaven - The Power to Animate |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting specific letters in multiple cells
Glad I could help and thanks for the feedback.
"tohlz" wrote: Thanks for the help, Andy and Mike. -- Shawn Toh (tohlz) Microsoft MVP PowerPoint (Amazing PowerPoint animations, artworks, games here) http://pptheaven.mvps.org PowerPoint Heaven - The Power to Animate "Mike H" wrote: Try =SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"O",""))) Mike "tohlz" wrote: Hi, Lets say I want to look for the letter "O" in a cell with the text "How are you?" and count the number of "O" in that particular cell, the formula would be something like: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"O","")) and the result for "How are you?" will return 2. My question is, what if I want to do it for multiple cells? For instance, from Cell A1 to A10. Thanks in advance. -- Shawn Toh (tohlz) Microsoft MVP PowerPoint (Amazing PowerPoint animations, artworks, games here) http://pptheaven.mvps.org PowerPoint Heaven - The Power to Animate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting specific letters in a cell | Excel Worksheet Functions | |||
How to count specific letters in range of cells? | Excel Discussion (Misc queries) | |||
Counting Specific Number of Days across Multiple Months | Links and Linking in Excel | |||
Counting text across multiple sheets with a specific criterion | Excel Worksheet Functions | |||
Counting multiple occurances of a specific string | Excel Worksheet Functions |