Change sum range depending on today's date

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I have a budget worksheet that uses a spending forecast sheet to determine where the spending level for each budget code should be to the end of the current month. In other words, the Forecasted YTD To Month End fomula for each budget code in the budget worksheet is simply:


    =SUM(Forecast!D8:F8)

    where column D is Jan, E is Feb, F is Mar etc in the forecast which distributes the budgeted amounts by month.


    In each row on the budget sheet I want to evaluate today's date and if we're in March I want the formula to read as above but when we move to April I want it to use G as the end column in the range to sum.


    SUMIF seems cumbersome in this circumstance and I wonder if a UDF might suit better. Any thoughts/help?


    TIA
    ML!

    Re: Change sum range depending on today's date


    Hello and welcome to Ozgrid.


    It may be best to upload a sample workbook (dummy data, exact structure) , but see if you can adapt this.


    Assumption:row 1, from column-D onward is a header row and contains valid dates (i.e. 1/1/2012, 2/1/2012, 3/1/2012, etc.)


    Row2 contains the values to sum.


    =SUMPRODUCT(--(MONTH(D1:H1)<=MONTH(TODAY())*(D2:H2)))


    There are other methods and a UDF does not seem to be needed here.


    For instance:


    If you created a dynamic named range for the header row (say we called this range "Header"), we could something like:


    =SUM(OFFSET($D$2,0,0,1,MATCH(TODAY(),Header,1)))


    Named range formula for header: =OFFSET(Sheet1!$D$1,0,0,1,COUNTIF(Sheet1!$1:$1,">0"))

    Re: Change sum range depending on today's date


    Thank you A...


    I appreciate such a quick response and great ideas. I am going to try to implement using your suggestions and if I can't make it work, I will do as you recommend and upload a dummy.


    Cheers!
    ML

    Re: Change sum range depending on today's date


    The OFFSET function with the named range works perfectly for my needs. Thanks again A!


    ML

    Re: Change sum range depending on today's date


    Resurrecting this thread for some similar help. Will someone illustrate OFFSET with this example so that I can try and adapt it to my workbook?


    Given the following:


    [TABLE="width: 801"]

    [tr]


    [td]

    CapEx

    [/td]


    [td]

    Apr-2014

    [/td]


    [td]

    May-2014

    [/td]


    [td]

    Jun-2014

    [/td]


    [td]

    Jul-2014

    [/td]


    [td]

    Aug-2014

    [/td]


    [td]

    Sep-2014

    [/td]


    [td]

    Oct-2014

    [/td]


    [td]

    Nov-2014

    [/td]


    [/tr]


    [tr]


    [td]

    Cap

    [/td]


    [td]

    35.69

    [/td]


    [td]

    37.49

    [/td]


    [td]

    35.07

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    Exp

    [/td]


    [td]

    -

    [/td]


    [td]

    33.23

    [/td]


    [td]

    32.62

    [/td]


    [td]

    33.71

    [/td]


    [td]

    8.70

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    Cap

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    22.87

    [/td]


    [td]

    23.84

    [/td]


    [td]

    23.84

    [/td]


    [td]

    23.08

    [/td]


    [td]

    14.61

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    Cap

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    33.23

    [/td]


    [td]

    33.71

    [/td]


    [td]

    32.62

    [/td]


    [td]

    8.70

    [/td]


    [td]

    -

    [/td]


    [/tr]


    [tr]


    [td]

    Exp

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    -

    [/td]


    [td]

    30.69

    [/td]


    [td]

    30.13

    [/td]


    [td]

    31.14

    [/td]


    [td]

    8.04

    [/td]


    [/tr]


    [/TABLE]


    Based on today's date, I need to sum past amounts and current/future amounts, broken down by capital & expense.forum.ozgrid.com/index.php?attachment/57598/

    Re: Change sum range depending on today's date


    Excel 2010 with free PowerPivot Add-In
    For the next generation of users.
    Even simpler. No helper column.
    Same link.

    Re: Change sum range depending on today's date


    SOLVED: I ended up using SUMPRODUCT:


    =SUMPRODUCT((Lifetime_Schedule<=TODAY())*(CapEx="Cap")*(Lifetime_Budget))

    Re: Change sum range depending on today's date


    mcfingrs,


    next time, please don't hijack others' threads. If you think it helps your case, then post a link to the existing thread in your own new thread.

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

Participate now!

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

Register Yourself Login