problems with arrays formulas and moving dates on by 1 month

Hi guys,

I need some help with an array formula,

In the attached spreadsheet I have sumed up column B $ Value for all dates that fall in September,

I was wondering if I can use a similar formula to look at t forecast, i need to move all the dates on by 1 month and then total all the $ values that occur 1 month later (ie during the month of October)

I have tried putting an EDATE formula into the array to see if this moves all dates on my 1 month but it returns an error

I was wondering if there is a formula to do this instead of me having to create a new column and move the dates on manually.

This needs to be repeated several times

Thanks
example.xlsx

You can use this formula instead of the array formula in F7:

=SUMIFS($B$3:$B$86553,$C$3:$C$86553,">="&F6,$C$3:$C$86553,"<="&DATE(YEAR(F6),MONTH(F6)+1,0))

Then you can just copy it into F11. It does not make use of column G dates, so those can be deleted.

Hope this helps.

Pete

apologies, ive looked at this again and realised i didnt quite explain this correctly. the dates in column c are fixed as they are a bill date, i need to roll all the dates on in column c by 1 month,
is it possible to include this into an array formula
the total $ value when the month in column c is rolled forward by 1 month should be the same as september (73,165,47).
i have attacehd an updated copy of the spreadsheet, the reason I need a formula to do this is because later on some of the $ values will need to drop off as I am doing a forecast.

Pl see the attached file.

You can use full-column references with SUMIFS with no loss of performance, so I have put this formula in G7 in KVSRINIVASAMURTHY's file for comparison:

Formula:

Please Login or Register  to view this content.

(coloured green), then copied to G11. As before, column H is not required (nor column D).

Hope this helps.

Pete

Columns C, D and E are the months in the year.
within each months column there are dates and I need to sum up the $ values.
the correct answer should be the same each month 53,652.52
as we are just rolling the months forward by 1 month.

the idea is to forecast out, some of the values will drop off after reaching a certain point but that is a different matter.

can we produce a formula to save having to creat columns D and E

the correct answers once we roll forward the billing cycle should always be the same 53,652.52