Felix asks for a Formula to Count the Cells that with a particular string, such as All Cells with the letter E? In Episode #1728, Bill shows us how to use COUNTIF with a Wildcard to Count the Cells or FIND and IFERROR in Conditional Formatting to highlight the Cells.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode: 1728.
Count Cells that Contain a Substring.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Felix.
Felix wants to know, how to set a formula to count.
The cells with a particular string say all words with the letter E.
Okay, so we have 500 words here on the screen.
I know that because of the COUNT A.
Okay, so to count the words that contain a particular substring, we use [ =COUNTIF ], and for the range, we'll specify our range words.
So, A2 to T26, comma and then in the Criteria, in quotes we can say star, E, star, like that.
And we'll press ENTER and there's 240 words that contain the letter E.
Let's try it with some other letter, and we'll try a.
162 or we can even try something like es.
How many times does an s following e.
You saw that's working. Great!
Now, I did a, an extra worksheet here, where I actually put in the letter that we're looking for up there.
So, we can type the letter and see the numbers, so 150 rs.
This formula was a little bit more complex because I had a concatenate, in quotes, the asterisk and then whatever's in D1.
And then the asterisk and I also did conditional formatting here to format the cells.
[ ALT+O D ] for conditional formatting.
And the rule there.
I used the FIND and FIND the lower of D1 in the lower ofA2.
If that ends up being an error, give me a zero.
Count how many are greater than 0 and so that will mark the cells.
If you need to see those, let's type es.
And it says that there's 11.
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11.
It's working. So, there we go.
I want to thanks Felix, for setting that question in.
Now, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode: 1728.
Count Cells that Contain a Substring.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent in by Felix.
Felix wants to know, how to set a formula to count.
The cells with a particular string say all words with the letter E.
Okay, so we have 500 words here on the screen.
I know that because of the COUNT A.
Okay, so to count the words that contain a particular substring, we use [ =COUNTIF ], and for the range, we'll specify our range words.
So, A2 to T26, comma and then in the Criteria, in quotes we can say star, E, star, like that.
And we'll press ENTER and there's 240 words that contain the letter E.
Let's try it with some other letter, and we'll try a.
162 or we can even try something like es.
How many times does an s following e.
You saw that's working. Great!
Now, I did a, an extra worksheet here, where I actually put in the letter that we're looking for up there.
So, we can type the letter and see the numbers, so 150 rs.
This formula was a little bit more complex because I had a concatenate, in quotes, the asterisk and then whatever's in D1.
And then the asterisk and I also did conditional formatting here to format the cells.
[ ALT+O D ] for conditional formatting.
And the rule there.
I used the FIND and FIND the lower of D1 in the lower ofA2.
If that ends up being an error, give me a zero.
Count how many are greater than 0 and so that will mark the cells.
If you need to see those, let's type es.
And it says that there's 11.
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11.
It's working. So, there we go.
I want to thanks Felix, for setting that question in.
Now, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
