Sum All Sheets Faster

Learn Excel from MrExcel Podcast, Episode 2090: The 3D Reference with Two Sets of Colons.

Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.  Our question actually came up in my seminar in Bloomington, Indiana.  And I usually talk about these 3D References where we have all of these different month names and then show how to build a spearing formula that will add up cell B4 on each of the sheets from January through December.  But what came up today was - No, we just want one formula that's going to sum the whole thing and yeah, that is possible =SUM and I want to come back here and click on January, choose all of the cells.  Let's get this out of the way.  Choose all the cells that I want to sum from the January sheet and then Shift-Click on December.  Watch this formula right now.  Right now it's pointing at 1 rectangular range on one sheet; when I Shift-Click on December, it rewrites it into a rectangular range on all of the sheets from January through December.  I mean, don’t have to click the closing parenthesis, just press Enter and it goes and grabs all of those numbers all the way across.  Cool, cool formula like that with 2 colons.  A  : in the sheet name and a : in the rectangular range we're pulling back. 

Now, what I usually show is just this little formula here where we have to do =sum(Jan:Dec!B4) like that - Regular 3D Reference but it also works 3D Reference pointing to an entire range, like that.

Hey, let's just do one more bonus trick here since this trick was so short.  If you have to add totals across the bottom and down the left- down the right-hand side, most people come here, hit the AutoSum, Copy across.  And then here, hit the AutoSum and Copy across.  Check out this awesome, awesome way.  I just choose all the numbers plus 1 extra column plus 1 extra row then hit the AutoSum and it adds the totals all the way around the outside.  Why did the number change?  Because these numbers are still ran between; but a cool couple of tricks.

3d References absolutely there in the book, Power Excel with MrExcel, the 2017 Edition.  Click that “i” on the top-right hand corner to read more about the book. 

Okay, episode wrap-up:  We're going to set my rectangular range on all worksheets, start off by typing =SUM click on the January sheet, highlight the range with the mouse and then Shift-Click on the December sheet, press Enter.  Don't even need the closing parenthesis or if you're looking for just a single cell =SUM(Jan:Dec! and the cell that we want.  A little bonus trick there at the end, adding the total row and column in one click of AutoSum.

Well, thanks for stopping by.  We'll see you next time for another netcast from MrExcel.