# 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:

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

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• 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/

## Files

• 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.

• Re: Change sum range depending on today's date

SOLVED: I ended up using SUMPRODUCT:

• Re: Change sum range depending on today's date

mcfingrs,