Loh from Malaysia sends in a very hard question. How can you use COUNTIF to analyze only the visible rows from a filtered data set? I knew the solution would have to involve iterating through multiple SUBTOTAL functions, but Aladin from the MrExcel Message board provides the solution. Episode 946 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, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see, if you can solve this problem.
Welcome back to the MrExcel netcast.
It's Monday, am I allowed to make your head completely spin.
My head has been completely spinning with this question from Low send in by Malaysia.
Below says hey, I have a filtered data set here I filtered it down to just the B's and I'm trying to use the COUNTIF function.
The kind of function to count how many of those records are two.
Oh hey, let's look one two the answer should be two, but COUNTIF is not ignoring the rows hidden by the filter and when I started to think about this.
Okay there's only one function I know of that will ignore the rows hidden by the filter, and that's the subtotal.
So, if I did a SUM of C3 to C60, it's going to sum everything, but if I did a SUBTOTAL, the subtotal 9 will ignore the rows hidden by the subtotal.
So, I started to think about this I said wow, if there's some way, that we could get it to evaluate each row in this range and do a subtotal.
Subtotal 3 comma would actually count that would give me a series of 1's and 0's and I made an attempt here a very bad attempt that did not work at all.
When I took a look at why evaluate formula I saw that basically the subtotal piece was being evaluated as a single number right here, the subtotal was not being evaluated as every single cell from C3 to C17.
It was just doing one evaluation which you know evaluates to true and that wasn't helping at all.
So, I said you know certainly Aladin at the MrExcel message board would have a way to solve this.
So, I just go out to Google and I search for COUNTIF Filtered and right there third result, MrExcel message board someone trying to do a very similar thing.
Now, it took a couple of replies here people trying, not understanding exactly what he was trying to do and then all of a sudden.
Ekim came in with one formula and then Aladin came in with a beautiful SUMPRODUCT formula. Aladdin is famous for these So, I adapted this formula, and we'll take a look at it Excel.
Basically, he's doing a subtotal, but he's doing it on this is generating an array.
So, it's going to do many subsets with one for every single row.
He checks to see if that is you know one or not if it's visible, he's gonna get a 1. If it's not visible, he's gonna get a zero and then he multiplies that by the criteria.
So, in this case it was we check it and see if column B was equal to a 2 instead of quality.
So, let's go take a look at this formula.
Right here, I'm going, well first of all we'll look at the formula as I adapted it.
We're looking at everything from C3 to C16, and then row of C3 to C16.
minus the min row of C3 to C16 all that's doing is, Coercing Excel into evaluating the subtotal once for every single row.
When we take a look at evaluate formula what we should see is that we generate an array there, we generate an array there.
And now, the subtotal command needs to do not just one subtotal, but many subtotals and they're perfect week.
Eventually, coerce it into 0's and 1's the 0's being the hidden rows the ones being the true rows.
Now, that I've gotten that.
Actually, I didn't get that Aladin got that, we then multiply it by our criteria check to see if C3 to C16 is equal to 2.
That's going to evaluate to false true. Which is basically 0's and 1's multiplied rightly together and we get the correct answer to and just to show you if we would choose a different item, maybe A's there's 2 there as well. Let's try something else the C's and there we only have 1, 2. So, the formula is working perfectly.
All right, so thanks to Low for sending in that question. If you've never used the MrExcel message board, this is a perfect example of how collaboratively folks out there can come up with formulas that make my head spin.
And thanks to Aladin. Aladin is the master of the SUMPRODUCT fromula.
He can, you know he can write formulas that will seemingly solve the impossible and to be honest, I don't always understand what they're doing I always take a look at evaluate formula, and I, I watch it work, and it's an amazing thing. So, there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, let's fire up a pivot table.
Let's see, if you can solve this problem.
Welcome back to the MrExcel netcast.
It's Monday, am I allowed to make your head completely spin.
My head has been completely spinning with this question from Low send in by Malaysia.
Below says hey, I have a filtered data set here I filtered it down to just the B's and I'm trying to use the COUNTIF function.
The kind of function to count how many of those records are two.
Oh hey, let's look one two the answer should be two, but COUNTIF is not ignoring the rows hidden by the filter and when I started to think about this.
Okay there's only one function I know of that will ignore the rows hidden by the filter, and that's the subtotal.
So, if I did a SUM of C3 to C60, it's going to sum everything, but if I did a SUBTOTAL, the subtotal 9 will ignore the rows hidden by the subtotal.
So, I started to think about this I said wow, if there's some way, that we could get it to evaluate each row in this range and do a subtotal.
Subtotal 3 comma would actually count that would give me a series of 1's and 0's and I made an attempt here a very bad attempt that did not work at all.
When I took a look at why evaluate formula I saw that basically the subtotal piece was being evaluated as a single number right here, the subtotal was not being evaluated as every single cell from C3 to C17.
It was just doing one evaluation which you know evaluates to true and that wasn't helping at all.
So, I said you know certainly Aladin at the MrExcel message board would have a way to solve this.
So, I just go out to Google and I search for COUNTIF Filtered and right there third result, MrExcel message board someone trying to do a very similar thing.
Now, it took a couple of replies here people trying, not understanding exactly what he was trying to do and then all of a sudden.
Ekim came in with one formula and then Aladin came in with a beautiful SUMPRODUCT formula. Aladdin is famous for these So, I adapted this formula, and we'll take a look at it Excel.
Basically, he's doing a subtotal, but he's doing it on this is generating an array.
So, it's going to do many subsets with one for every single row.
He checks to see if that is you know one or not if it's visible, he's gonna get a 1. If it's not visible, he's gonna get a zero and then he multiplies that by the criteria.
So, in this case it was we check it and see if column B was equal to a 2 instead of quality.
So, let's go take a look at this formula.
Right here, I'm going, well first of all we'll look at the formula as I adapted it.
We're looking at everything from C3 to C16, and then row of C3 to C16.
minus the min row of C3 to C16 all that's doing is, Coercing Excel into evaluating the subtotal once for every single row.
When we take a look at evaluate formula what we should see is that we generate an array there, we generate an array there.
And now, the subtotal command needs to do not just one subtotal, but many subtotals and they're perfect week.
Eventually, coerce it into 0's and 1's the 0's being the hidden rows the ones being the true rows.
Now, that I've gotten that.
Actually, I didn't get that Aladin got that, we then multiply it by our criteria check to see if C3 to C16 is equal to 2.
That's going to evaluate to false true. Which is basically 0's and 1's multiplied rightly together and we get the correct answer to and just to show you if we would choose a different item, maybe A's there's 2 there as well. Let's try something else the C's and there we only have 1, 2. So, the formula is working perfectly.
All right, so thanks to Low for sending in that question. If you've never used the MrExcel message board, this is a perfect example of how collaboratively folks out there can come up with formulas that make my head spin.
And thanks to Aladin. Aladin is the master of the SUMPRODUCT fromula.
He can, you know he can write formulas that will seemingly solve the impossible and to be honest, I don't always understand what they're doing I always take a look at evaluate formula, and I, I watch it work, and it's an amazing thing. So, there you have it.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
