+ Reply to Thread
Results 1 to 6 of 6

Dynamic Sums

  1. 12-03-2020, 06:28 PM #1
    xTyD23x
    xTyD23x is offline
    Registered User
    Join Date
    02-18-2020
    Location
    Dallas TX
    MS-Off Ver
    365
    Posts
    33

    Dynamic Sums

    I am using a couple columns that are summed up in a cell at the bottom of a worksheet, but the rows in each column are dynamic. This is NOT a table and rows are added and deleted from the base file we use as a template every time. When rows are deleted, the calculations are all still good, obviously. When rows are added (either by copying existing rows and inserting the copied row, or by just inserting a new row) the sum formula doesn't update to include the new rows. I'm sure this is relatively simple, but I can't figure out how to fix this. Thanks in advance for any help!
    Reply With QuoteRegister To Reply
  2. 12-03-2020, 06:35 PM #2
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,940

    Re: Dynamic Sums

    Insert a blank row between the last item in your range and the total. Then when you add a new item, insert a row and then the blank row will always be there and the totals will adjust.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 12-04-2020, 05:42 AM #3
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,625

    Re: Dynamic Sums

    Data starts from Row 2.
    Formula in last row

    =SUM(INDIRECT("$A$2:$A"& ROW()-1))

    If you don't want passive formula

    =SUM(A$2:INDEX(A:A, ROW()-1))
    Last edited by kvsrinivasamurthy; 12-04-2020 at 05:44 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  4. 12-04-2020, 10:17 AM #4
    xTyD23x
    xTyD23x is offline
    Registered User
    Join Date
    02-18-2020
    Location
    Dallas TX
    MS-Off Ver
    365
    Posts
    33

    Re: Dynamic Sums

    Thanks guys!!!
    Reply With QuoteRegister To Reply
  5. 12-04-2020, 10:30 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,430

    Re: Dynamic Sums

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 12-04-2020, 10:40 AM #6
    BMV
    BMV is offline
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Dynamic Sums

    You can use smart table and Total row also.

    one remark
    =SUM(A$2:INDEX(A:A, ROW()-1)) is preferable because not volatile.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to create a dynamic array which sums sales to a specific client based on product
    By SrAlx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2019, 05:43 PM
  2. [SOLVED] Dynamic range sums
    By benitoski in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-04-2019, 11:03 AM
  3. Sums of varying number of cells. Sums of parts of a column depending on content
    By lotsof in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2019, 08:00 AM
  4. Create a list that sums values of selected names in a dynamic range
    By PAexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-18-2014, 11:23 AM
  5. Can I get a Max of Sums?
    By smarmentor in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-06-2014, 07:08 PM
  6. [SOLVED] HOW DO I DO IF SUMS
    By junior willams in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2005, 07:05 AM
  7. Sums
    By Jet in forum Excel General
    Replies: 5
    Last Post: 01-13-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1