variable sum formula

[DanKokoro]

Hi all,

Have a question relating to a variable sum formula. I did a fair amount of searching online but couldn't find an answer to my problem. Would be very appreciative of any help. Here goes.

I am after a formula, that will do a vertical lookup and then sum a variable number of cells to the right.

E.G.
In row 2 i have the 12 months starting at B2.
In Column A i have product codes starting at A3, they are sequential and are as follows 001,002,003,004 etc..

In cell A1 i have a variable product code
In cell A2 i have a variable number which designates how many months (1-12) . i.e. if the number in A2 is 2 it means two months (Jan and Feb), if the number is 4 it means 4 months (Jan, Feb, Mar, April).

for example if A1 is "0003" and A2 is 4, i want a formula that will look down to find "0003" which will be in A5 and then sum A6,A7,A8,A9.

I'm sorry if my explanation/examples are not as helpful as they could be. But i'd really appreciate some help on this because i've been stuck for ages on this one...

Thanks so much in advance..

 

[Aladin Akyurek]

Try...

=SUM(INDEX($B$3:$B$200,MATCH(A1,$A$3:$A$200,0)):INDEX($B$3:$M$200,MATCH(A1,$A$3:$A$200,0),A2))

where A1 houses a product of interest and A2 number of months.

 

[DanKokoro]

perfect.. exactly what i wanted. Thanks heaps!!

 

[Aladin Akyurek]

perfect.. exactly what i wanted. Thanks heaps!!

You are welcome. Thanks for providing feedback.