+ Reply to Thread
Results 1 to 4 of 4

How to find sum of cells that's 20 cells before the last cell

  1. #1
    Registered User
    Join Date
    11-11-2015
    Location
    Cabramatta West
    MS-Off Ver
    Excel 2013
    Posts
    8

    How to find sum of cells that's 20 cells before the last cell

    Hello,

    So I have a column of ongoing numbers and I want to automatically find a formula that will sum the last 20 numbers as I input more into the column. For example, in the book, the last number is 33, but I will put in 34, and 35 as time goes on as shown. Is there a formula that will automatically calculate the last 20 numbers as I input more numbers over time?

    Any help will be greatly appreciated! Thanks!

    Book1.xlsx

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to find sum of cells that's 20 cells before the last cell

    You actually have 21 rows of numbers highlighted in each column.

    Anyway, try

    =SUM(OFFSET(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),0,0,-20,1))

    If what you highlighted is what you actually want, change the -20 to -21
    Last edited by Jonmo1; 12-16-2015 at 09:27 AM.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to find sum of cells that's 20 cells before the last cell

    If there are ever less than 20 (or 21) values in the column, it will error.
    That can be handled like this

    =SUM(OFFSET(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),0,0,-MIN(21,COUNT(A:A)),1))

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: How to find sum of cells that's 20 cells before the last cell

    Try

    =SUM(OFFSET($A$1,MATCH(999999,A1:A100,1),0,-21))

    In your sample you are counting 21 days

+ 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. [SOLVED] Use .Cells.Find(What:=) to find values in Multiple Cells
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-13-2014, 02:39 PM
  2. Cant find how to get average dates difference between averaged cells, w/out extra cells
    By jeremyharrisonspo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-10-2014, 08:20 PM
  3. Looking to create a macro that will skip blank cells in a column, find data cells
    By crayzwalz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2013, 09:01 AM
  4. Replies: 7
    Last Post: 12-04-2012, 06:23 AM
  5. Replies: 1
    Last Post: 08-08-2012, 08:40 PM
  6. [SOLVED] Find cells which contain specific word, delete cells and shift left.
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2012, 07:00 AM
  7. Find relevent cells to copy and paste from a preset text in cells
    By dazza67 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2012, 04:39 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