Use a spearing formula or a 3D Reference to sum Jan through Dec. Episode recap:
You need to total numbers on Jan, Feb, Mar, … Dec
You could =Jan+Feb+Mar…
But there is a faster way called a Spearing formula
Also known as a 3D reference
Start by typing =SUM(
Click on first sheet
Shift-click on last sheet
Click on the cell
Type ) and press Enter
Anything between Jan through Dec is included
Don't add new sheets with Grocery List 7 gallons of milk
Don't randomly move August outside
Mack Wilk trick using Start and End sheets Podcast 1836
Bizarre Bob Umlas wildcard trick =SUM('J*'!B4)
Thanks to Othneil Denis for suggesting 3D Formula Reference
You need to total numbers on Jan, Feb, Mar, … Dec
You could =Jan+Feb+Mar…
But there is a faster way called a Spearing formula
Also known as a 3D reference
Start by typing =SUM(
Click on first sheet
Shift-click on last sheet
Click on the cell
Type ) and press Enter
Anything between Jan through Dec is included
Don't add new sheets with Grocery List 7 gallons of milk
Don't randomly move August outside
Mack Wilk trick using Start and End sheets Podcast 1836
Bizarre Bob Umlas wildcard trick =SUM('J*'!B4)
Thanks to Othneil Denis for suggesting 3D Formula Reference
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1984 - Sum All Sheets!
Well hey, I'll be podcasting the whole book, go ahead and click that card there to subscribe to the playlist for all of these videos.
Welcome back to MrExcel netcast, I'm Bill Jelen.
Today we're going to talk about how to create a summary sheet.
So in the past couple of podcasts, I've created these reports from January through December.
And now I need to create a summary report.
So I'm going to take the December report, and drag it over, and I'll call it "Entire Year".
Alright, and what we have to do here, is we have to add up January + February + March, and of course, it's really tempting, it's really tempting to put an = there, and go click on January in that cell, +, and then go click on February in that cell, +, You eventually could do it, right?
Although it would be really bad if it was 52 weeks, week 1 through week 52, that woud take forever, so there's a much faster way to go, it's called A Spearing Formula, or a 3D reference, I'm going to show you, a beautiful way to build this.
We start off by typing =SUM( . All right now, very carefully click on January.
That was a regular click, then, shift click on the last sheet, that's December, starts to build the syntax up here.
And then click on the cell we want to add up.
So before ) , Enter!
Right, and what that's done, is that's created a formula that's spearing through all of the sheets, adding up cell B4 on each one.
Now, of course, all the sheets have to be the same shape.
But in this case since I did a fast worksheet copy, we know that's true.
Couple of things here, If you take a look at the formula, they don't have any apostrophes (') around there, they had the ' when we were building it.
You have to put the ' in, if the sheets have a space.
So, January 2018, then that formula will automatically rewrite, to include ' around it.
Do the ' have to be there or not?
Well, it depends on what the sheet names are.
But if you use the trick that I showed, you don't have to worry about that.
Hey, I have one bizarre difference here.
This is from Bob, I'm listed(?) in one of his books, he showed me this trick.
Let's say that you had quarters here: Q1, Q2, Q3 and Q4.
You wounded up only the quarterly sheets.
Now I'm just going to use anything that starts with J, instead of anything that starts with a Q.
So =SUM(' and then what we're looking for, in my case I'm looking for a J* , and that wild card will actually work, close the ' , and then add cell B4.
When I press ENTER, they're going to rewrite that formula, and include all of the sheets that started with a J. So Jan,Jun:Jul!
Bizarre, bizarre way to go.
So, if you've been enjoying this free podcast series, I want to encourage you to go out and buy the whole book, it will cover all 40 tips, you'll have a great reference guide, it's cheap, it's 25$, I really appreciate it.
Episode Recap: Alright so, we have two total numbers on all sheets, from January through December, you could build a really long formula, but there's a faster way.
It's either called a Spearing Formula - 3D Reference.
Fast way to do this, start by typing =SUM( , click on the first sheet, shift-click on the last sheet, click on the cell you want, and then the ) and press Enter.
Now, obviously don't add new sheets to the middle.
Right, so if you're summing January through December, don't go to August, insert a new worksheet, a grocery list with seven gallons of milk, don't randomly take August and move it outside a December, Now, if you go back to a great, great model off podcast episode 1836, where Mack woud have shown his favorite trick, of two blank sheets, one called Start>, one called <End, that way he knows everything between those are the ones being added up.
And then Bob Umlas has a cool wild card trick that I just showed you.
And again the book it was crowdsourced, people sent in their their favorite tips, so, Neil Dennis suggested the 3D Formula References, one of his favorite tips, want to thank him for sending it in.
And I want to thank you for stopping by, We'll see you next time for another netcast from MrExcel!
Well hey, I'll be podcasting the whole book, go ahead and click that card there to subscribe to the playlist for all of these videos.
Welcome back to MrExcel netcast, I'm Bill Jelen.
Today we're going to talk about how to create a summary sheet.
So in the past couple of podcasts, I've created these reports from January through December.
And now I need to create a summary report.
So I'm going to take the December report, and drag it over, and I'll call it "Entire Year".
Alright, and what we have to do here, is we have to add up January + February + March, and of course, it's really tempting, it's really tempting to put an = there, and go click on January in that cell, +, and then go click on February in that cell, +, You eventually could do it, right?
Although it would be really bad if it was 52 weeks, week 1 through week 52, that woud take forever, so there's a much faster way to go, it's called A Spearing Formula, or a 3D reference, I'm going to show you, a beautiful way to build this.
We start off by typing =SUM( . All right now, very carefully click on January.
That was a regular click, then, shift click on the last sheet, that's December, starts to build the syntax up here.
And then click on the cell we want to add up.
So before ) , Enter!
Right, and what that's done, is that's created a formula that's spearing through all of the sheets, adding up cell B4 on each one.
Now, of course, all the sheets have to be the same shape.
But in this case since I did a fast worksheet copy, we know that's true.
Couple of things here, If you take a look at the formula, they don't have any apostrophes (') around there, they had the ' when we were building it.
You have to put the ' in, if the sheets have a space.
So, January 2018, then that formula will automatically rewrite, to include ' around it.
Do the ' have to be there or not?
Well, it depends on what the sheet names are.
But if you use the trick that I showed, you don't have to worry about that.
Hey, I have one bizarre difference here.
This is from Bob, I'm listed(?) in one of his books, he showed me this trick.
Let's say that you had quarters here: Q1, Q2, Q3 and Q4.
You wounded up only the quarterly sheets.
Now I'm just going to use anything that starts with J, instead of anything that starts with a Q.
So =SUM(' and then what we're looking for, in my case I'm looking for a J* , and that wild card will actually work, close the ' , and then add cell B4.
When I press ENTER, they're going to rewrite that formula, and include all of the sheets that started with a J. So Jan,Jun:Jul!
Bizarre, bizarre way to go.
So, if you've been enjoying this free podcast series, I want to encourage you to go out and buy the whole book, it will cover all 40 tips, you'll have a great reference guide, it's cheap, it's 25$, I really appreciate it.
Episode Recap: Alright so, we have two total numbers on all sheets, from January through December, you could build a really long formula, but there's a faster way.
It's either called a Spearing Formula - 3D Reference.
Fast way to do this, start by typing =SUM( , click on the first sheet, shift-click on the last sheet, click on the cell you want, and then the ) and press Enter.
Now, obviously don't add new sheets to the middle.
Right, so if you're summing January through December, don't go to August, insert a new worksheet, a grocery list with seven gallons of milk, don't randomly take August and move it outside a December, Now, if you go back to a great, great model off podcast episode 1836, where Mack woud have shown his favorite trick, of two blank sheets, one called Start>, one called <End, that way he knows everything between those are the ones being added up.
And then Bob Umlas has a cool wild card trick that I just showed you.
And again the book it was crowdsourced, people sent in their their favorite tips, so, Neil Dennis suggested the 3D Formula References, one of his favorite tips, want to thank him for sending it in.
And I want to thank you for stopping by, We'll see you next time for another netcast from MrExcel!
