AutoSum from Top

June 28, 2018 - by Bill Jelen

AutoSum is the first formula that most people learn in Excel. Today's question: Is there a way to place the AutoSum at the top of a column of numbers?

I know a lot of AutoSum tricks. But I don't know a good way to AutoSum at the top of the column. Most people know that AutoSum works at the bottom of a column of numbers:

AutoSum also can add a total to the right side of a row of numbers:

AutoSum can even add totals at the bottom and right of a range in one keystroke. Select all of your numbers plus the extra row and column. Then press AutoSum or use Alt + = shortcut.

The result: many SUM formulas added at once:

All of those totals were added in a single click.

But I ran into Lisa in New Jersey who needed to add totals at the top of a column. I've asked around, hoping someone like Bob Umlas would know a crazy trick like Shift + Alt + Equals while holding Ctrl and wearing a magic hat. But I haven't found anyone who knows. If you do know a trick, go to the YouTube video below and leave a comment with the answer.

What keystroke would add totals to the top?

My solution is a macro such as this:

Sub AutoSumDown()
    Dim OrigCell As Range
    Dim FirstCell As Range
    Dim LastCell As Range
    Set OrigCell = ActiveCell
    Set FirstCell = ActiveCell.Offset(1, 0)
    Set LastCell = FirstCell.End(xlDown)
    MyFormula = "=SUM(" & FirstCell.Address & ":" & LastCell.Address & ")"
    OrigCell.Formula = MyFormula
End Sub

Watch Video

Video Transcript

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.

Download Excel File

To download the excel file: autosum-from-top.xlsm

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"You can lead a manager to data, but you can’t make him think"

Jordan Goldmeier

Title Photo: Oliver Schwendener on Unsplash