Today, Amelie asks how to add Two sets of Subtotals, with the Subtotals appearing at the top! In Episode #1498 of the Learn Excel from MrExcel Podcast Series, Bill shows us how to get the Subtotals.
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1498: Dual Subtotals At The Top.
Well, hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question is sent in by -- I don't know how to pronounce it -- it looks like Amelie but she's from Belgium so I'm sure it has some great, great foreign French-sounding pronunciation.
Amelie. I don't know. Who knows?
Amelie’s trying to -- I'm sorry I'm just going to call you Emily -- is trying to put subtotals, two levels, both for REGION and PRODUCT, but, a twist, at the top, at the top.
Now, normally when we do this, we would start subtotals first by region, then by product.
So, I'm going to try the same thing here.
I will go out to DATA, SUBTOTAL, AT EACH CHANGE IN REGION, use the SUM function, for SALES, and instead of SUMMARY BELOW DATA, I'm going to uncheck that, and the summary should appear above the data.
So, let's try it.
Okay.
There we go.
So, EAST TOTAL is at the top and then WEST TOTAL.
Now, let's go back in.
DATA, SUBTOTAL, and EACH CHANGE IN PRODUCT, uncheck REPLACE CURRENT SUBTOTALS.
So, now, that is not an option for us anymore, it's automatically going to be above the data, and click OK, and now what we have is we have all these A products with an A TOTAL, all of these B products with a B TOTAL, and then all of the EAST REGION with an EAST TOTAL.
So, it seems to me that it's relatively straightforward to do which makes me think that Amelie’s trying to do something else.
Perhaps she wants the PRODUCT totals at the bottom and the EAST total at the top, or maybe she doesn’t like the GRAND TOTAL there at all.
If it's an issue of the GRAND TOTAL, well, we can just delete that row, but to get the A and B at the bottom and the EAST at the top, that's just not going to work.
If we're looking for everything at the top, well, then, we're good to go.
What we have here is going to work.
If it's the other way where some products, like, the product is at the bottom and then the East is at the top, I just don't have a good solution.
If you have a good solution, shoot it to me, bill@mrexcel.com, and we'll get you on a future podcast.
Well, hey.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1498: Dual Subtotals At The Top.
Well, hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question is sent in by -- I don't know how to pronounce it -- it looks like Amelie but she's from Belgium so I'm sure it has some great, great foreign French-sounding pronunciation.
Amelie. I don't know. Who knows?
Amelie’s trying to -- I'm sorry I'm just going to call you Emily -- is trying to put subtotals, two levels, both for REGION and PRODUCT, but, a twist, at the top, at the top.
Now, normally when we do this, we would start subtotals first by region, then by product.
So, I'm going to try the same thing here.
I will go out to DATA, SUBTOTAL, AT EACH CHANGE IN REGION, use the SUM function, for SALES, and instead of SUMMARY BELOW DATA, I'm going to uncheck that, and the summary should appear above the data.
So, let's try it.
Okay.
There we go.
So, EAST TOTAL is at the top and then WEST TOTAL.
Now, let's go back in.
DATA, SUBTOTAL, and EACH CHANGE IN PRODUCT, uncheck REPLACE CURRENT SUBTOTALS.
So, now, that is not an option for us anymore, it's automatically going to be above the data, and click OK, and now what we have is we have all these A products with an A TOTAL, all of these B products with a B TOTAL, and then all of the EAST REGION with an EAST TOTAL.
So, it seems to me that it's relatively straightforward to do which makes me think that Amelie’s trying to do something else.
Perhaps she wants the PRODUCT totals at the bottom and the EAST total at the top, or maybe she doesn’t like the GRAND TOTAL there at all.
If it's an issue of the GRAND TOTAL, well, we can just delete that row, but to get the A and B at the bottom and the EAST at the top, that's just not going to work.
If we're looking for everything at the top, well, then, we're good to go.
What we have here is going to work.
If it's the other way where some products, like, the product is at the bottom and then the East is at the top, I just don't have a good solution.
If you have a good solution, shoot it to me, bill@mrexcel.com, and we'll get you on a future podcast.
Well, hey.
I want to thank everyone for stopping by.
We'll see you next time for another netcast from MrExcel.
