+ Reply to Thread
Results 1 to 4 of 4

Last 12 not empty cells sum

  1. #1
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Czech republic
    MS-Off Ver
    Excel 2010
    Posts
    119

    Last 12 not empty cells sum

    Dear excel funs, reporting colleagues, ...
    I would like to ask you for help with an idea how to make 12 months floating summary for each line.
    Enclosed, you have a file with data-example.
    So far, I found a formula to ask for the last cell, so I can add it in formula 12 times, but I am sure, there is a better way. SUMMIFS with some condition in additional row (if(actual date is lover than (now - 12); 1; 0) - summifs 1.
    but it seems not nice to me.

    Thanks for idea.
    Attached Files Attached Files

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

    Re: Last 12 not empty cells sum

    Assuming there will always be at least 12 cells filled out..

    =SUM(OFFSET(INDEX(E3:AN3,MATCH(9.999999999E+307,E3:AN3)),0,0,1,-12))

  3. #3
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Czech republic
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Last 12 not empty cells sum

    Seems a syntax in my language mutation for "offset" is different, but thanks. I will work on this idea an I hope I will get a result soon. Great.

  4. #4
    Forum Contributor
    Join Date
    08-22-2012
    Location
    Czech republic
    MS-Off Ver
    Excel 2010
    Posts
    119

    Re: Last 12 not empty cells sum

    Hello, offset is a great function. Enclosed, you can find my sollution. If you open in your excel, it will automaticallly translate function and its syntax, so it should be working.
    Thanks one more time to all of you.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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