Need to Sum YTD Dynamically, So when 1 month is over it drops off and the new month is add

Hello!

I am working with Dynamic Ranges for the first time. I have figured out the rest of my table, but I cannot get the YTD column to calculate correctly. It continues to add on from the beginning with data from last year. I only want to show the total for the last 12 months. I have attached the sheet and highlighted the cells. I currently have a simple sum function in Row 6 to show that it just continues to add up, which is what I need to avoid. I have been trying combinations of the Offset function, but I am getting no where. I know I am making this more difficult than it needs to be!

Copy of Warranty Cost Tracking.xlsx

Keeping rest of your formulas same, you could have simply used this in L5 and copy across to the right-

Please Login or Register  to view this content.

For a one stop formula just use this in B6 and copy across-

Please Login or Register  to view this content.

B6:

Formula:

Please Login or Register  to view this content.

and copy across.


Regards, TMS

Another possibility

In B6 copied right

=SUM(OFFSET($B$5, 0, MAX(COLUMNS($B$6:B$6)-12,0),1, MIN(12, COLUMNS($B$6:B$6))))

How would you define "works best". With all due respect to ChemistB, it is the only formula that uses a volatile function, OFFSET. Generally speaking, they are to be avoided, particularly if they are used to "excess".

Thanks for the rep

and rewriting of my range formula I got my chart to work as well.

Might be worth sharing your updated sample workbook so that others may benefit from a fully working example.

Regards, TMS

Thank you

In defense of my formula, while I freely admit it's not the best, with the amount of data in this sheet, it shouldn't create any noticeable lag. The key with volatile functions is knowing when you are able to use them and when you can't.

No criticism, just a question and comparison. My question was "how do you define works best"? ... that, given that three solutions all produced the same, the required, and the correct answer. The comparison being that your formula used a volatile function. The concern being that it may, eventually, be dragged across many months and years. Add to that, the dynamic named ranges for the chart are also volatile.

My point was not that your formula was not the best, nor that the others were better.

So, no defence necessary; your point regarding using volatile functions wisely and is well made and accepted.


Regards, TMS