Back to episode 1728, Felix asked about counting cells that contain a certain substring. It turns out that he wanted to count only the visible cells. This is possible with VBA, but I can not find a regular Excel formula that would do it. AGGREGATE would offer some hope, but that function won't accept arrays for the first 14 functions, of which, COUNT is one. Plus, AGGREGATE won't detect hidden columns. So, in today's episode, a tiny UDF function that looks for a substring, then checks to see if the row and column are visible.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1731 - Count Visible with Substring!
Hey welcome back, and I have our own channels at YouTube, and when someone posts a question, that shows up in an email.
Alright, so an email from Felix showed up and said "How do you set a formula to count the visible cells within a particular string, say, all the words with the letter E?" And I said "Well, that's really funny." He said Visible, I'm sure he didn't really mean Visible, and so my episode 1728 this week showed how to count all of the cells that contain a letter E. But then I actually went and looked at the video.
Oh, he has posted that on Count Visible Rows, he really was looking for just the Visible.
I knew we were in trouble, because I immediately thought "Well OK, subtotal aggregate." AGGREGATE is going to be the one that we want to use.
But I remember that AGGREGATE doesn't allow an array until you get up to functions, I think is 14 and above, is that right?
I didn't pull the book out this morning to look, but that's almost what I remember you saying.
So you know, here's a COUNT of all those cells, here's the COUNTA of the visible cells.
And it's funny, AGGREGATE is ignoring hidden rows, not hidden columns.
But then, to try and do that to match everything with the letter "es" up here in the formula bar, well it's just not going to allow that array, since we're using function 3, and function 3 is COUNTA.
So I'm like, "Oh wow, OK, I'm going to have to switch over and do some VBA to solve this." Alt+F11 to insert module, and then typed out this module.
CVM stands for Count Visible Match, I could have called it CountVisibleMatch, but I figured that would be evil, to make Felix have to type =CountVisibleMatch( what we're looking for.
And then pass the range that we want to look through.
Initialize the function CVM to be =0, because whatever value's in the variable, CVM at the end gets passed back to the worksheet.
So we look through each cell in the range, instead of using FIND or COUNTIF that I used back in episode 728.
Use the VBA function in string, InStr.
Our starting position 1, look through the lowercase of Cell.Value for the lowercase of FindWhat, that will return its position.
If it's greater than 0, then we know that we have a match.
And if that's TRUE, check and see if the cell's entire row is not hidden, check and see if the cell's entire column is not hidden.
If all three of those are TRUE, then CVM = CVM + 1.
Why don't I use an AND here?
Well, because I figure it's going to be rare that we find a match, and I only want to check for the hidden row and hidden column on the 11 that are matching, instead of all 500 cells.
So here's our function CVM.
We'll switch back to Excel, and =CVM, and we are looking for "es", comma, in A2-T26, press Enter, and it's giving us the 6 because there are 1-2-3-4-5-6, there's actually 11!
Using the formula from 1728 probably hidden(?) some of those rows and some of those columns.
Oh hey, there you have it, I want to thank Felix for sending that question in episode 1728, in 1731 finally got the meaning of his question, 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 1731 - Count Visible with Substring!
Hey welcome back, and I have our own channels at YouTube, and when someone posts a question, that shows up in an email.
Alright, so an email from Felix showed up and said "How do you set a formula to count the visible cells within a particular string, say, all the words with the letter E?" And I said "Well, that's really funny." He said Visible, I'm sure he didn't really mean Visible, and so my episode 1728 this week showed how to count all of the cells that contain a letter E. But then I actually went and looked at the video.
Oh, he has posted that on Count Visible Rows, he really was looking for just the Visible.
I knew we were in trouble, because I immediately thought "Well OK, subtotal aggregate." AGGREGATE is going to be the one that we want to use.
But I remember that AGGREGATE doesn't allow an array until you get up to functions, I think is 14 and above, is that right?
I didn't pull the book out this morning to look, but that's almost what I remember you saying.
So you know, here's a COUNT of all those cells, here's the COUNTA of the visible cells.
And it's funny, AGGREGATE is ignoring hidden rows, not hidden columns.
But then, to try and do that to match everything with the letter "es" up here in the formula bar, well it's just not going to allow that array, since we're using function 3, and function 3 is COUNTA.
So I'm like, "Oh wow, OK, I'm going to have to switch over and do some VBA to solve this." Alt+F11 to insert module, and then typed out this module.
CVM stands for Count Visible Match, I could have called it CountVisibleMatch, but I figured that would be evil, to make Felix have to type =CountVisibleMatch( what we're looking for.
And then pass the range that we want to look through.
Initialize the function CVM to be =0, because whatever value's in the variable, CVM at the end gets passed back to the worksheet.
So we look through each cell in the range, instead of using FIND or COUNTIF that I used back in episode 728.
Use the VBA function in string, InStr.
Our starting position 1, look through the lowercase of Cell.Value for the lowercase of FindWhat, that will return its position.
If it's greater than 0, then we know that we have a match.
And if that's TRUE, check and see if the cell's entire row is not hidden, check and see if the cell's entire column is not hidden.
If all three of those are TRUE, then CVM = CVM + 1.
Why don't I use an AND here?
Well, because I figure it's going to be rare that we find a match, and I only want to check for the hidden row and hidden column on the 11 that are matching, instead of all 500 cells.
So here's our function CVM.
We'll switch back to Excel, and =CVM, and we are looking for "es", comma, in A2-T26, press Enter, and it's giving us the 6 because there are 1-2-3-4-5-6, there's actually 11!
Using the formula from 1728 probably hidden(?) some of those rows and some of those columns.
Oh hey, there you have it, I want to thank Felix for sending that question in episode 1728, in 1731 finally got the meaning of his question, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
