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. **
Bookmarks