+ Reply to Thread
Results 1 to 12 of 12

Sum of Last 12 cells in the same row

  1. #1
    Registered User
    Join Date
    05-23-2017
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2010
    Posts
    23

    Sum of Last 12 cells in the same row

    For starters, I know how to do a standard =SUM.
    what I am looking for is to get the sum of the last 12 cells in a row based on the furthest filled cell to the right.
    for example - Jessica - the last cell filled is K2, so I would want all cells from K2 and left 12 cells to be summed. (I want this one to stop at B2)
    Matt, I would want cells Z3 to sum the previous 12 cells in that row. so Z3 through O3.

    The issue I am having is that I want to sum to change when I add a value in that row. So now if I add a number in cell AE2, Jessica's totals would now change because AE2 and 12 rows to the left would be a different value.

    I hope this makes sense to those of you that are going to try to help me.

    Thanks in advance,
    Attached Files Attached Files
    Last edited by 7Twenty_Two; 12-02-2020 at 06:19 PM.

  2. #2
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Sum of Last 12 cells in the same row

    Where should the results appear? What are the results (values) you are expecting for the sample data? What if there are fewer than 12 values altogether?
    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.

  3. #3
    Registered User
    Join Date
    05-23-2017
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2010
    Posts
    23

    Re: Sum of Last 12 cells in the same row

    Great questions.
    I should have left room in Column B for the results.
    if there are fewer than 12 results, i would want the formula to stop summing at the first data entered in the row.

  4. #4
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Sum of Last 12 cells in the same row

    Have a go with this in B2 copied down:

    =IFERROR(SUM(AE2:INDEX(B2:AE2,LARGE(COLUMN(B2:AE2)*(B2:AE2<>"")-1,12))),SUM(AE2:INDEX(B2:AE2,LARGE(COLUMN(B2:AE2)*(B2:AE2<>""),12))))

    Once you have made room, that is.

    You can make the ranges as wide as you want.

  5. #5
    Registered User
    Join Date
    05-23-2017
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2010
    Posts
    23

    Re: Sum of Last 12 cells in the same row

    ERROR - Microsoft Excel cannot calculate a formula. there is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the undo command.

    after I click "ok", the formula does show up, but the value in the cell (b2) is "0"

  6. #6
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Sum of Last 12 cells in the same row

    My error!

    Try again:

    =IFERROR(SUM(AF2:INDEX(C2:AF2,LARGE(COLUMN(C2:AF2)*(C2:AF2<>"")-2,12))),SUM(AF2:INDEX(C2:AF2,LARGE(COLUMN(C2:AF2)*(C2:AF2<>""),12))))

  7. #7
    Registered User
    Join Date
    05-23-2017
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2010
    Posts
    23

    Re: Sum of Last 12 cells in the same row

    Let me start over so I can give a better picture of what I am looking for, as i don't feel i was clear from my original post... which is my fault.

    I am updating a FMLA spreadsheet for my company. if you're not familiar with FMLA, you get 12 weeks (480 hours) of protected leave while you are out due to an injury/illness/baby. you get 12 weeks per year. So i need a spreadsheet that will help me calculate the previous 12 months of hours that i entered into my spreadsheet.
    so in the example spreadsheet i provided, if Tom used 3 hours (AE4) in June 30th, 2021, i need to only count the data in the cells from the previous 12 months in that row. I do not want to count the hours he has in May of 2020 because that is over 12 months ago.
    the formula should give me the total of 58. If it gives me 67, i know that the formula calculated all of the numbers in that row.

  8. #8
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Sum of Last 12 cells in the same row

    OK - we are now talking about months and hours, not the last 12 entries.

    PLEASE provide a more meaningful and representative sample sheet WITH your expected results added manually.

    Yes, you have completely shifted the goalposts! And yes, my formula DOES work on the sheet you attached above, which clearly now is not fit for purpose, and does what you originally asked (I checked it).

  9. #9
    Registered User
    Join Date
    05-23-2017
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2010
    Posts
    23

    Re: Sum of Last 12 cells in the same row

    Alright, I think I have now given you a better idea of what it is I am looking for.
    I couldn't figure out how to add an attachment in my response, so I went in and did an edit to the original post.
    instructions are in the attachment.
    Hopefully this time I explained it better.

    Again, thanks for all the help. I truly to appreciate it!!!

  10. #10
    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. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,392

    Re: Sum of Last 12 cells in the same row

    In the first workbook you showed dates. Now you are showing pay periods. Which is it in the real data? If dates, then it will probably be easier.

    Just providing one line of sample data is NOT enough.

    I am not yet convinced that the sample data is close enough to the real data to spend time working on a solution, so I'll wait until you have responded to this.

  11. #11
    Registered User
    Join Date
    05-23-2017
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2010
    Posts
    23

    Re: Sum of Last 12 cells in the same row

    Alright, I have changed the attachment again to more accurately look like the actual spreadsheet I will be using.
    instructions are in the attachment.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Sum of Last 12 cells in the same row

    Try pasting the following formula into cell J3 and then drag the fill handle down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 4
    Last Post: 02-11-2015, 09:02 AM
  2. Replies: 0
    Last Post: 01-21-2015, 12:05 PM
  3. [SOLVED] Linking cells globally to allow users the ability to change cells on separate sheet/cells.
    By V1gilante in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2014, 10:47 PM
  4. combining many cells in one cells keeping character font and hyperlinks of all cells
    By mankind00 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2012, 09:41 AM
  5. Replies: 4
    Last Post: 06-17-2011, 08:53 AM
  6. Formula: Drop Down List -> Choose Option Finds Cells & Replace Cells with Cells
    By g00glethis1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2010, 01:10 PM
  7. Replies: 2
    Last Post: 06-24-2010, 04:53 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1