A Power Excel audience member in Omaha asked about the difference between 9 and 109 in the Excel SUBTOTAL function.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2269.
Subtotal 9 versus 109.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen. I was in Omaha doing a power Excel seminar and a question came up about this formula here. The subtotal 9 formula. What’s the difference between 9 and 109?
And let me illustrate this. So the subtotal 9 is designed for when you’re hiding rows by filtering. Alright, so if I would filter just the red team that 88, 98 changes the 66106. Alright, or if I choose just the blue team then that subtotal is working great.
Alright. But if it’s not a filter, right.
If we just have all the data and I’m randomly hiding rows. So right here, Alt O R H and I’ve hidden the row. See that, 8098 is not getting rid of the things that I manually hid. So what I have to do is come in here and say subtotal 109 like this 109 and now it will respect the things that I manually hid, alright. And this is explained in Excel help, the 1 to 11 versus 101 to 111, you know.
There’s 11 functions there. 9 is the one for some, that’s the one I use most often.
Now what’s interesting here is when I first did this, you know, I actually think that there’s a bug here because if I go to 9, just the 9 formula, that should include everything, will format row, unhide Alt O R U for format row, unhide, apply the filter again And the first thing I do so we’re at 8,098.
First thing I do is I, you know, choose a filter. Let’s take guava and elderberry out. Alright, so the number updates. But then I’m going to further hide something. I want to take this cherry and format row hide and that number updated, right. That’s not suppose to happen. I’m using the 9. It’s not suppose to ignore the hidden things but apparently because I’ve already filtered now 9 is doing both things that are filtered and things that I manually hid which seems to be a bug. I’ll have to pass it on to the Excel team. Kind of a weird, weird one there. So alright, to whoever asked me that question in Omaha, there’s the solution.
And hey, I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
Subtotal 9 versus 109.
Hey welcome back to the MrExcel netcast, I’m Bill Jelen. I was in Omaha doing a power Excel seminar and a question came up about this formula here. The subtotal 9 formula. What’s the difference between 9 and 109?
And let me illustrate this. So the subtotal 9 is designed for when you’re hiding rows by filtering. Alright, so if I would filter just the red team that 88, 98 changes the 66106. Alright, or if I choose just the blue team then that subtotal is working great.
Alright. But if it’s not a filter, right.
If we just have all the data and I’m randomly hiding rows. So right here, Alt O R H and I’ve hidden the row. See that, 8098 is not getting rid of the things that I manually hid. So what I have to do is come in here and say subtotal 109 like this 109 and now it will respect the things that I manually hid, alright. And this is explained in Excel help, the 1 to 11 versus 101 to 111, you know.
There’s 11 functions there. 9 is the one for some, that’s the one I use most often.
Now what’s interesting here is when I first did this, you know, I actually think that there’s a bug here because if I go to 9, just the 9 formula, that should include everything, will format row, unhide Alt O R U for format row, unhide, apply the filter again And the first thing I do so we’re at 8,098.
First thing I do is I, you know, choose a filter. Let’s take guava and elderberry out. Alright, so the number updates. But then I’m going to further hide something. I want to take this cherry and format row hide and that number updated, right. That’s not suppose to happen. I’m using the 9. It’s not suppose to ignore the hidden things but apparently because I’ve already filtered now 9 is doing both things that are filtered and things that I manually hid which seems to be a bug. I’ll have to pass it on to the Excel team. Kind of a weird, weird one there. So alright, to whoever asked me that question in Omaha, there’s the solution.
And hey, I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
