Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

    • RevTimms
    • By RevTimms 5th Jan 18, 10:31 AM
    • 52Posts
    • 46Thanks
    excel formula
    • #1
    • 5th Jan 18, 10:31 AM
    excel formula 5th Jan 18 at 10:31 AM
    I have a column of figures on my spreadsheet. eg A3 to A41.
    I will sometimes be adding to the column and sometimes deleting from it.
    eg delete A5, or adding another figure eg at A42.
    Elsewhere on the spreadsheet I want, the first one and the last one totalled, but I don't want to have to keep altering the new cell everytime I add or delete a value.
    Is there a formula which would do this for me?
Page 1
    • jack_pott
    • By jack_pott 5th Jan 18, 10:43 AM
    • 4,467 Posts
    • 5,769 Thanks
    • #2
    • 5th Jan 18, 10:43 AM
    • #2
    • 5th Jan 18, 10:43 AM
    There's no need to do anything, that's the way Excel works already. If you put =A3+A41 in the cell where you want the total, then it will automatically increment/decrement the references when you insert/delete lines. (If you don't want it to automatically alter references you use a dollar symbol in front of the part you want to freeze: =A$3+A$41)
    I'm not upset that you lied to me, I'm upset that from now on I can't believe you.
    Friedrich Nietzsche
    • WaywardDriver
    • By WaywardDriver 5th Jan 18, 10:59 AM
    • 99 Posts
    • 104 Thanks
    • #3
    • 5th Jan 18, 10:59 AM
    • #3
    • 5th Jan 18, 10:59 AM
    If I understand it you want A3+Axx where xx = last row in column. If so try
    = A3 + INDIRECT("A" & MATCH(, A : A,-1))
    and remove all spaces.
    [Part of earlier version without spaces was displayed as emoji]
    Last edited by WaywardDriver; 05-01-2018 at 12:19 PM.
    • DoaM
    • By DoaM 5th Jan 18, 12:30 PM
    • 4,166 Posts
    • 4,195 Thanks
    • #4
    • 5th Jan 18, 12:30 PM
    • #4
    • 5th Jan 18, 12:30 PM
    Without spaces.
    Diary of a madman
    Walk the line again today
    Entries of confusion
    Dear diary, I'm here to stay
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

4,905Posts Today

4,350Users online

Martin's Twitter