+ Reply to Thread
Results 1 to 13 of 13

Auto populate Sum formula in Total column for each new row added

  1. #1
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Auto populate Sum formula in Total column for each new row added

    Excel '07

    ColumnA | ColumnB | ColumnC | ColumnD| ColumnE | ColumnF | ColumnG |
    data | 123 | data | 456 | data | 789 | =SUM(B2,D2,F2)



    How can I sum columns B, D & F in column G without the need to copy the formula each time a new row is added?

    Users enter about 10 new rows of data each day and I would prefer that I don't have to copy the formula to a few thousand rows every once and awhile, nor expect the users to correctly copy the formula.

    I believe I did this once using an ARRAY but I don't remember how I did it.

    Thanks in advance!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto populate Sum formula in Total column for each new row added

    how about just =SUM(A2:F2)
    it will ignore the text parts.
    then just drag down.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Auto populate Sum formula in Total column for each new row added

    are you just asking how to sum columns B,D and F?
    If so just use this
    =SUM(B:B,D:D,F:F)

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Auto populate Sum formula in Total column for each new row added

    now that I see what dosydos gave you AND reread what you wrote in post #1, if columns are what you want then something as simple as =SUM(A:F) or SUM(B:F) would work though it isn't that different than what dosydos gave you.

  5. #5
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: Auto populate Sum formula in Total column for each new row added

    That goes against my statement "don't have to copy the formula to a few thousand rows..."

  6. #6
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: Auto populate Sum formula in Total column for each new row added

    Quote Originally Posted by Sambo kid View Post
    now that I see what dosydos gave you AND reread what you wrote in post #1, if columns are what you want then something as simple as =SUM(A:F) or SUM(B:F) would work though it isn't that different than what dosydos gave you.
    I'm not looking for "how to sum". I'm looking for "Auto populate (the column) with the sum formula (ONLY AFTER/WHEN) each new row is added". Your suggestion requires either the user to copy and paste the formula for each new row they add or it requires me to pre-populate the column with the formula every so often.

  7. #7
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Auto populate Sum formula in Total column for each new row added

    why not just put my formula in cell G1 that i posted in post 3 and not have to copy ever?

    also for future sake you can hover the cursor to the bottom right of the cell with the formula, it will turn into a skinny cross. Just double click and it will copy the formula all the way down to the last row of data, even 100,000 rows in one second if necessary.

  8. #8
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: Auto populate Sum formula in Total column for each new row added

    Or you could preporpulate all of golumn G with this formula.
    Put in G1 and drag down a few hunder thousand rows if you want.

    =IF(AND(B1="",D1="",F1=""),"",SUM(B1,D1,F1))

    if theres no data in B D or F the formula will be blank and you wont even know its there

  9. #9
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: Auto populate Sum formula in Total column for each new row added

    Quote Originally Posted by dosydos View Post
    why not just put my formula in cell G1 that i posted in post 3 and not have to copy ever?

    also for future sake you can hover the cursor to the bottom right of the cell with the formula, it will turn into a skinny cross. Just double click and it will copy the formula all the way down to the last row of data, even 100,000 rows in one second if necessary.
    Because your formula gives a grand total of all values. Column G should display the sum of the other columns only within that row.

  10. #10
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: Auto populate Sum formula in Total column for each new row added

    Quote Originally Posted by dosydos View Post
    Or you could preporpulate all of golumn G with this formula.
    Put in G1 and drag down a few hunder thousand rows if you want.

    =IF(AND(B1="",D1="",F1=""),"",SUM(B1,D1,F1))

    if theres no data in B D or F the formula will be blank and you wont even know its there
    Because your solution doesn't answer my question of How to "auto populate Sum formula". Copying any formula, using any method, to rows that do not have data, is what I want to avoid.

  11. #11
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Auto populate Sum formula in Total column for each new row added

    You could auto populate any formulas by making your range of data a Table:
    - select all data, including headers
    - press CTRL+T
    - check My table has headers
    - click OK

    Now any formula you add to the table it will auto populate in all rows of the table.
    If you add new rows at the end of the table, they will be automatically included in the table and the formula will auto populate on the newly included rows.

  12. #12
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: Auto populate Sum formula in Total column for each new row added

    Quote Originally Posted by Mrrrr View Post
    You could auto populate any formulas by making your range of data a Table:
    - select all data, including headers
    - press CTRL+T
    - check My table has headers
    - click OK

    Now any formula you add to the table it will auto populate in all rows of the table.
    If you add new rows at the end of the table, they will be automatically included in the table and the formula will auto populate on the newly included rows.
    Perfect!!

    I tried this before I originally posted but it didn't work for me. Since you suggested it I figured I must have done something wrong. When I first created it, I selected all columns ($A:$G). When I added data to the next available field neither the formatting nor the formulas copied. Upon receiving your post, I selected the data only ($A1:$G1211). When I added a record to the next available row (R1212) all formatting and formulas automatically populated that row. Thanks Mrrr!! That was exactly what I was looking for.

  13. #13
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Auto populate Sum formula in Total column for each new row added

    You are welcome and thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. auto adjust total amount whenever a new row is added/ deleted
    By Vnz in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-18-2019, 02:08 AM
  2. Replies: 6
    Last Post: 12-07-2016, 02:36 AM
  3. Replies: 6
    Last Post: 12-07-2016, 12:28 AM
  4. Replies: 1
    Last Post: 12-06-2016, 06:28 PM
  5. Replies: 4
    Last Post: 05-02-2013, 04:39 PM
  6. auto populate a formula in a cell as new rows of data are added
    By CharterJP in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-30-2012, 06:43 PM
  7. Replies: 3
    Last Post: 02-28-2012, 11:54 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1