Thread: Dynamic Range Reference, Varying Size of Range

Hello!

I would like to reference a range in a sum formula, but the range size depends on some other number called "months left". I have a formula that does this, but I am wondering if there is an easier, more efficient, way.

A very simplified example: my range with numbers is A2:A6, if month left is 1, I want my formula to return A2, if months left is 2, I want my formula to give me A2+A3, if months left is 3, I want my formula to give me A2+A3+A4

Here's my approach:

=SUM(IF(MonthsLeft=1,A2,IF(MonthsLeft=2,A2:A3,IF(MonthsLeft=3,A2:A4,IF(MonthsLeft=4,A2:A5,A2:A6)))))

and so on, for up to 12 months.

This does what I want, but can you tell me a nicer way? Is a nicer way even possible? As this is mostly out of curiosity and desire to learn, don't rack your brain, I don't mind if I don't get an answer.

I added an attachment.

Thanks. **