Sum two columns with changing ranges

    Hello
    I have this problem:
    I have to sum 2 different columns based on changing data: The 2 columns are monthly values organized by year.
    I have to sum THE LAST TWELVE MONTHS (that will change with each month added).
    Any suggestions ??


    Thanks

    Re: Sum two columns with changing ranges


    Are you summing 12 months back to the day or to the beginning of the month? Are we going to Nov 1, 2015 or Nov 28, 2015

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Sum two columns with changing ranges


    Thanks for your reply, NBVC
    I just need to sum all the values of a month, regardless of first day or the last day
    In the image provided, I need to sum October, November and December 2015 and all of 2016.
    The proble in that the formula has to change when I put new data to a month in 2016, because I need to add only November and december 2015 and all of 2016


    [ATTACH=CONFIG]70724[/ATTACH]


    Thanks

    Re: Sum two columns with changing ranges


    See attached - seems to do what you ask, but not extensively test (your job).
    Study the formulas and adjust range and cell references accordingly.


    The following named ranges are used:
    CountRw =COUNT(CurrentYr)
    CurrentYr =OFFSET(INDIRECT("Sheet1!"&ADDRESS(5,CurrYrCol)),0,0,12,1)
    CurrYrCol =MATCH(YEAR(TODAY()),Sheet1!$4:$4,1)
    PriorYr =OFFSET(INDIRECT("Sheet1!"&ADDRESS(5,CurrYrCol-1)),IF(CountRw<12,CountRw,0),0,12-CountRw,1)
    Year_Header =OFFSET(Sheet1!$A$4,0,1,1,COUNT(Sheet1!$4:$4))


    A Pivot Table would be useful here.



    forum.ozgrid.com/index.php?attachment/70851/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login