Welcome back to the MrExcel netcast. It’s Monday. Am I allowed to make your head completely spin? On my head has been completely spinning with this question from Loh sent in by Malaysia, Loh says, “Hey, I have a filter data set here. I filtered it down to just the Bs and I’m trying to use the Countif function. The Countif to count how many of those records are two. 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 it will ignore the rows hidden by the filter, and that's the subtotal, so if I did the sum of C3 to C60 it’s going to sum everything, but if I did a subtotal. The subtotal nine will ignore the rows hidden by the subtotal, so I started to think about this. I said, “Well, if there’s some way that we could get it to evaluate each row in this range and do a subtotal three column will actually count that will give me a series of ones and zeros and I made an attempt here, a very bad attempt that it did not work at all when I took a look at why I 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 evaluate the true and that was not helping at all.
So I said, “You know certainly Aladdin at the MrExcel message board would have a way to solve this, so I just go out to Google and I searched for Countif, filter and right there, their result MrExcel message board someone trying to do a very similar thing. Now it took a couple of replies here, people are trying not to understand exactly what he was trying to do, and then all of the sudden he can came in with one formula and then Aladdin came in with a beautiful sum product formula.
Aladdin is famous for this so I adapted his formula and we’ll take a look at it in Excel. Basically he's doing a subtotal, but he's doing it on this is generally in a race so it's going to do many subtotal for one for every single row. He checked to see if that is one or not. If it’s visible it's going to get a one and if it’s not visible it’s going to get a zero and then he multiplies that by the criteria, so in this case we check it and see if column B was equal to a two instead of quality.
So let's go and take a look at this formula right here. Well, first of all we’ll look at formula as I adapted it. We’re looking at everything from C3 to C16, and then row of C3 to C16 minus the mean row of C3 to C16 all that’s doing is coercing Excel into evaluating the subtotal ones for every single row and we’ll take a look and 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. We eventually coerced it into zeros and ones, the zeros being the hidden rows, the ones being the true rows.
Now that I've gotten that as exciting I get a lot. I’ve got that, we then multiply it by our criteria, check to see if C3 to C16 is equal two. That's going to evaluate to false through which is basically zeros and ones. All apply that together and we get the correct answer to and just to show you if we would choose a different item maybe as.
There’s two there as well. Let’s try something else, the Cs and then we only have one, two so the formula is working perfectly. Alright, so thanks to Loh for sending in that question. If you've never use 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 Aladdin.
Aladdin is the master of the sum product formula. 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 and evaluate formula and I watch it working 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.
Transcription by:
Scribe4you Transcription Services