Mystery in Excel: How can you AutoSum at the top of a column of numbers?
Does anyone have a great solution? Note it in the YouTube comments. Win a prize.
My lame solution is a macro
To download this workbook: https://www.mrexcel.com/download-center/2018/06/autosum-from-top.xlsx
Does anyone have a great solution? Note it in the YouTube comments. Win a prize.
My lame solution is a macro
To download this workbook: https://www.mrexcel.com/download-center/2018/06/autosum-from-top.xlsx
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2220: AutoSum from the Top??
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Alright, so the AutoSum, the very first formula almost anyone learned, AutoSum will add up a column of numbers.
Just come out here below the column and press the AutoSum button, press Enter.
It also works going across, so if I have-- one, two, three-- Ctrl+Enter, and I say over here, AutoSum or Alt+Equals.
Well, I have that.
What people don't realize is that if you need to add totals at the bottom, and at the right, you can just select all the numbers, extra row, extra column, Alt+Equals or AutoSum, and it totals all the way around.
All of those tricks-- I know-- all those tricks are great.
But I was in New Jersey doing a seminar and someone said, “Look, I need to AutoSum at the top.” I was like, “The top?” “Yeah, I have tons of columns.
I always need to put the total at the top.
Different number of items each time.” And I'm like, wow.
You would think there would have to be some way to auto sum at the top.
So I went to Bob Omus-- Bob Omus knows every weird trick there is-- I was counting on Bob to tell me, Oh, yeah, all you have to do is hold down the Ctrl+Alt+Shift and then press Alt+Equals, you know, pat your head and close your eyes and press Alt+Equals or something.
Bob doesn't know, so now I'm turning to you, the brightest Excel viewers on the entire planet, who can figure out a faster better easier way for almost everything I do here on YouTube.
How would you AutoSum at the top?
Now, my completely horrible, lame method here, was to go into VBA, and this macro where I figure out we're going to start from the original cell, the first cell is going to be one cell below that, and from that cell I'm going to press the Ctrl+Down Arrow to get to the last cell, and then build a formula of the FirstCell.Adress colon LastCell.Address.
Now, this is going to fail if there's only one number below.
It has to have at least two numbers below, and then put that formula in the original cell.
So we'll try that.
Here, I added it to the Quick Access Toolbar, I AutoSum down, so I choose that right arrow, AutoSum down, AutoSum down, AutoSum down, AutoSum down.
Yeah, it kind of works, but, you know, and then I'm going to have to have this macro in there.
There has to be a better way.
Now, hey, a plug for my book, "MrExcel LIVe, The 54 Greatest Tips of All Time".
Click the "I" on the top right hand corner to check that out.
And then, the mystery in Excel, how can you AutoSum at the top of a column of numbers?
Does anyone have a great solution?
Put it down in the YouTube comments?
Valuable, valuable prize.
Well, okay, it's only worth fifty cents, but still it's a cool prize.
And then my lame solution is a macro.
But there has to be some better way.
To download the workbook from today's video, visit that URL in the YouTube description.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Alright, so the AutoSum, the very first formula almost anyone learned, AutoSum will add up a column of numbers.
Just come out here below the column and press the AutoSum button, press Enter.
It also works going across, so if I have-- one, two, three-- Ctrl+Enter, and I say over here, AutoSum or Alt+Equals.
Well, I have that.
What people don't realize is that if you need to add totals at the bottom, and at the right, you can just select all the numbers, extra row, extra column, Alt+Equals or AutoSum, and it totals all the way around.
All of those tricks-- I know-- all those tricks are great.
But I was in New Jersey doing a seminar and someone said, “Look, I need to AutoSum at the top.” I was like, “The top?” “Yeah, I have tons of columns.
I always need to put the total at the top.
Different number of items each time.” And I'm like, wow.
You would think there would have to be some way to auto sum at the top.
So I went to Bob Omus-- Bob Omus knows every weird trick there is-- I was counting on Bob to tell me, Oh, yeah, all you have to do is hold down the Ctrl+Alt+Shift and then press Alt+Equals, you know, pat your head and close your eyes and press Alt+Equals or something.
Bob doesn't know, so now I'm turning to you, the brightest Excel viewers on the entire planet, who can figure out a faster better easier way for almost everything I do here on YouTube.
How would you AutoSum at the top?
Now, my completely horrible, lame method here, was to go into VBA, and this macro where I figure out we're going to start from the original cell, the first cell is going to be one cell below that, and from that cell I'm going to press the Ctrl+Down Arrow to get to the last cell, and then build a formula of the FirstCell.Adress colon LastCell.Address.
Now, this is going to fail if there's only one number below.
It has to have at least two numbers below, and then put that formula in the original cell.
So we'll try that.
Here, I added it to the Quick Access Toolbar, I AutoSum down, so I choose that right arrow, AutoSum down, AutoSum down, AutoSum down, AutoSum down.
Yeah, it kind of works, but, you know, and then I'm going to have to have this macro in there.
There has to be a better way.
Now, hey, a plug for my book, "MrExcel LIVe, The 54 Greatest Tips of All Time".
Click the "I" on the top right hand corner to check that out.
And then, the mystery in Excel, how can you AutoSum at the top of a column of numbers?
Does anyone have a great solution?
Put it down in the YouTube comments?
Valuable, valuable prize.
Well, okay, it's only worth fifty cents, but still it's a cool prize.
And then my lame solution is a macro.
But there has to be some better way.
To download the workbook from today's video, visit that URL in the YouTube description.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
