Rolling 12 formula that changes when i add a new row to the table

[jondavis1987]

=SUMIF(A$10:A46,">=" & DATE(YEAR(A46),MONTH(A46)-11,DAY(A46)),B$10:B46)

I have this formula in column C of a table. It calculates the rolling 12. The problem is when I add a row the formula in C46 changes to =SUMIF(A$10:A47,">=" & DATE(YEAR(A46),MONTH(A46)-11,DAY(A46)),B$10:B47)

It keeps adding more when i do another month

 

[braindiesel]

I am happy to try and help you but the problem you explained is what excel does...
Is what you are hoping for that the new 47th row will have no value as it now falls outside of the desired 46?

Book2
	A	B
1	0
2
Sheet1

Cell Formulas
Range	Formula
A1	A1	=SUMIF(A$10:OFFSET(A9,37,0),">=" & DATE(YEAR(OFFSET(A9,37,0)),MONTH(OFFSET(A9,37,0))-11,DAY(OFFSET(A9,37,0))),B$10:OFFSET(A9,37,1))

Does this solve it?

If not, provide a data sample and this will be easier to solve

 

[jondavis1987]

I haven’t tried this out yet because I’m not at work.
I have a table. A10:A46 have a date. B10:B46 have totals. C10:C46 have a formula to calculate the total of only the last twelve months. So whatever month is in A46 should be the Most recent month C46 will calculate.

when I add information into A47 and B47 the formula in C46 changes to include A47 and B47. That’s never been how I’ve seen a formula in excel work. The formula produce in C47 should have A47 and B47 included and excludes the Earliest one that C46 had accounted for