Mike Girvin and Bill Jelen meet up again to offer three solutions to the problem of counting how many records match a wildcard criteria. Episode 980 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, I'm Bill Jelen from MrExcel.Com.
And I've got a cool Excel tip for you today.
Hey, this is Mike Gel Girvin.
I am Excel Is Fun on YouTube.
and I have a different way to do that.
Hey, it's Bill Jelen.
Want to welcome back.
We're going to do another dueling Excel podcast here, Mike Girvin and I had so much fun when I was out in Seattle.
We're gonna try and do these once a week where I solve a problem and then Mike solves a problem.
Today this problem actually came from MrsExcel.
Someone center this data where she had a whole bunch of records.
She had a count.
How many records had a D, how many records had an M and how many records had a P.
And so I attacked this with probably the long way around.
I just added a series of ones over here in the right hand column and I said alright let's turn on the filter.
And we'll go into the Service column and do a text filter.
And say we want to see how many contain the D.
Now, a regular COUNT function or a SUM function is going to count the hidden rows as well.
So, I'm going to use the subtotal function up here.
And say that I want to count all of our cells.
And I'm actually going to type in [ C2: ] I think it goes down to 200 or so C2 to C205.
All right, so we have 133 records that have a D, and then it's a relative pain I guess to go through and change this each time.
M...
We see if there's nine or 26 M's and then same thing with P.
I Figure out, how many P's are.
So, it's functional.
It's solve the problem, but I bet that Mike has a different and probably better way to do this.
Let's throw it over to Mike.
Hey, thanks MrExcel And MrsExcel for this great question.
Hey, MrExcel deceives the subtotal function and filter What's great about this method is he didn't have to mess around with the criteria and the Syntax for Criteria when he used the filter Now, I'm going to show you slightly different way.
We are going to have to do the messy syntax for the criteria, but the advantages will get to see all three results at once .
Now, I'm going to put a label here.
This will be our Count column tab and this will be our criteria Now our criteria is going to be D.
We got to count D, M and P.
And why don't we use the COUNTIF function.
[ =COUNTIF ] and the range is the same.
I'm going to click here Notice that it's not filter.
Click in the top cell.
[ CTRL+Shift+down-arrow ] to highlight all the way down and then F4.
F4 not only locks it because we're going to need this range to be locked on only copy it down.
But it also jumps a screen back in view.
Now, comma.
What is the criteria.
Well, for this particular formula, it's D.
Now, we can't just put in quotes D because it will only look for D's.
And right now, I don't think I see any.
We need to somehow tell this that there could be some letters afterwards or there could even be some letters before.
So, here's how you do it.
Use a wildcard.
Asterisk means one or more characters.
So if we put one before and after, you can have characters before and after.
Now, I got to put a close parenthesis And I could copy this down right and then quickly change this to an M.
Oh! This is already getting a time-consuming.
Much better way to do this.
Let's delete this and we'll edit this formula.
No way!
We can put the first asterisk in quotes and use the join symbol because notice we have our criteria one cell to our left.
So, ampersand.
And then I want to go and get that.
Hey, look I'm using my arrow key to try and grab that E2 I'm going to hit the F2 key.
That allows me to use my arrow key.
You could also type it in.
Ampersand because we have to join to that on the right side.
Double quote, asterisks, double quote close parenthesis.
What this is done, and if we if you highlight this right here And hit the F9 key, which is evaluate.
You can see it gives us the same thing.
I'm going to [ CTRL+Z ] because I don't want that hard coded.
[ CTRL+Enter ] and then I'm going to double click and send it down.
And sure enough it got it right.
I'm going to click in the last cell and hit F2.
Just to double-check.
Sure enough I've got that and that.
Alright! I am going to throwback to MrExcel.
Yeah, alright Mike great solution.
I love that one.
Mike also pointed out to me There's a slightly a faster way to do.
My solution, I do [ CTRL+T ] in Excel 2007 or [ CTRL+L ] in the old Excel 2003.
And then I need to go in and use the contains filters, really important, I do that first.
So, I'll say contains D And we'll go down to the bottom of this data set.
Do [ ALT+ = ] down here, and that doesn't put in a SUM function it puts in a SUBTOTAL function.
I'm going to change the 109 to 103 3 being count.
And you see that we have a 133 that contain a D.
I can also then come up here and change that text filter to an M And Click OK and the formula now updates to show us 126.
So, several different ways to go there.
Well, hey, I hope you enjoyed this dueling podcast.
Drop us a note Bill@mrexcel.com Let us know if you think we should keep doing this.
I realize that ran long.
I will try and short it down next time and keep it down under five minutes.
Alright! I want to thank you for stopping by on behalf of Mike and myself.
We'll see you next time for another netcast from MrExcel.
And I've got a cool Excel tip for you today.
Hey, this is Mike Gel Girvin.
I am Excel Is Fun on YouTube.
and I have a different way to do that.
Hey, it's Bill Jelen.
Want to welcome back.
We're going to do another dueling Excel podcast here, Mike Girvin and I had so much fun when I was out in Seattle.
We're gonna try and do these once a week where I solve a problem and then Mike solves a problem.
Today this problem actually came from MrsExcel.
Someone center this data where she had a whole bunch of records.
She had a count.
How many records had a D, how many records had an M and how many records had a P.
And so I attacked this with probably the long way around.
I just added a series of ones over here in the right hand column and I said alright let's turn on the filter.
And we'll go into the Service column and do a text filter.
And say we want to see how many contain the D.
Now, a regular COUNT function or a SUM function is going to count the hidden rows as well.
So, I'm going to use the subtotal function up here.
And say that I want to count all of our cells.
And I'm actually going to type in [ C2: ] I think it goes down to 200 or so C2 to C205.
All right, so we have 133 records that have a D, and then it's a relative pain I guess to go through and change this each time.
M...
We see if there's nine or 26 M's and then same thing with P.
I Figure out, how many P's are.
So, it's functional.
It's solve the problem, but I bet that Mike has a different and probably better way to do this.
Let's throw it over to Mike.
Hey, thanks MrExcel And MrsExcel for this great question.
Hey, MrExcel deceives the subtotal function and filter What's great about this method is he didn't have to mess around with the criteria and the Syntax for Criteria when he used the filter Now, I'm going to show you slightly different way.
We are going to have to do the messy syntax for the criteria, but the advantages will get to see all three results at once .
Now, I'm going to put a label here.
This will be our Count column tab and this will be our criteria Now our criteria is going to be D.
We got to count D, M and P.
And why don't we use the COUNTIF function.
[ =COUNTIF ] and the range is the same.
I'm going to click here Notice that it's not filter.
Click in the top cell.
[ CTRL+Shift+down-arrow ] to highlight all the way down and then F4.
F4 not only locks it because we're going to need this range to be locked on only copy it down.
But it also jumps a screen back in view.
Now, comma.
What is the criteria.
Well, for this particular formula, it's D.
Now, we can't just put in quotes D because it will only look for D's.
And right now, I don't think I see any.
We need to somehow tell this that there could be some letters afterwards or there could even be some letters before.
So, here's how you do it.
Use a wildcard.
Asterisk means one or more characters.
So if we put one before and after, you can have characters before and after.
Now, I got to put a close parenthesis And I could copy this down right and then quickly change this to an M.
Oh! This is already getting a time-consuming.
Much better way to do this.
Let's delete this and we'll edit this formula.
No way!
We can put the first asterisk in quotes and use the join symbol because notice we have our criteria one cell to our left.
So, ampersand.
And then I want to go and get that.
Hey, look I'm using my arrow key to try and grab that E2 I'm going to hit the F2 key.
That allows me to use my arrow key.
You could also type it in.
Ampersand because we have to join to that on the right side.
Double quote, asterisks, double quote close parenthesis.
What this is done, and if we if you highlight this right here And hit the F9 key, which is evaluate.
You can see it gives us the same thing.
I'm going to [ CTRL+Z ] because I don't want that hard coded.
[ CTRL+Enter ] and then I'm going to double click and send it down.
And sure enough it got it right.
I'm going to click in the last cell and hit F2.
Just to double-check.
Sure enough I've got that and that.
Alright! I am going to throwback to MrExcel.
Yeah, alright Mike great solution.
I love that one.
Mike also pointed out to me There's a slightly a faster way to do.
My solution, I do [ CTRL+T ] in Excel 2007 or [ CTRL+L ] in the old Excel 2003.
And then I need to go in and use the contains filters, really important, I do that first.
So, I'll say contains D And we'll go down to the bottom of this data set.
Do [ ALT+ = ] down here, and that doesn't put in a SUM function it puts in a SUBTOTAL function.
I'm going to change the 109 to 103 3 being count.
And you see that we have a 133 that contain a D.
I can also then come up here and change that text filter to an M And Click OK and the formula now updates to show us 126.
So, several different ways to go there.
Well, hey, I hope you enjoyed this dueling podcast.
Drop us a note Bill@mrexcel.com Let us know if you think we should keep doing this.
I realize that ran long.
I will try and short it down next time and keep it down under five minutes.
Alright! I want to thank you for stopping by on behalf of Mike and myself.
We'll see you next time for another netcast from MrExcel.
