How to Sum Data in Columns using a formula for the column

[grantmt]

Hi,

I would like to sum the data in mulitple columns -in this case b4 through aj4.
However, I would like to use a formula that sums the data based on an input value, where the value input equals the final column used in the sum.

Example: if I input a 2 into cell AN6, I would like a formula in AN7 that sums column b4 through c4. If I put a 3 into cell AN6, AN7 returns the sum of the value in columns b4 through d4.

I am using excel 2010.

There are multiple rows (200) that I will perform this function on, so I would rather not define a range for each row.

Thanks in advance for assistance

 

[DonkeyOte]

You can use INDEX

AN7:
=SUM($B4:INDEX($B4:$IV4,$AN$6))

You could also use OFFSET but this would be Volatile

AN7:
=SUM(OFFSET($B4,0,0,1,$AN$6))

 

[grantmt]

The Index function works perfectly. Thank you very very much. I use Excel every day but have never used Index.