Adding a range of numbers based on a numerical input

[merlyn45]

Hi guys & gals,

Im stumped and need some help here. I'm trying to output a total of a range of cells, but the range will vary depending on a user input in another cell. Let me explain: On my "data" worksheet I have columns of numbers. The column A is Week #. On sheet 2 I want to show the sums for all or part of 9 columns based on the Week # that is entered in the cell C1 on the "data" worksheet. So if the user inputs "22" I need to generate the totals of column B, C, D etc from week 1 to 22 on the "overall report 2012" worksheet (I should note that there may be data in the columns for week 23, 24, etc). I have attached an example worksheet in case I haven't made a bit of sense in my explanation.

[Cutter]

Try this in A10 and dragged over

=SUM('Overall Report 2012'!B2:INDEX('Overall Report 2012'!B:B,$C$1+1))

[merlyn45]

Try this in A10 and dragged over

=SUM('Overall Report 2012'!B2:INDEX('Overall Report 2012'!B:B,$C$1+1))

Works great! Thanks! Could you explain how this formula works? I want to learn how this works so I can gain more experience with Excel.

[merlyn45]

I understand this formula up to the INDEX where row or column is stated...how does $c1+1 work?

[Cutter]

Sorry, missed your first request.

The $C1+1 specifies the row number for the INDEX() function.
Because you have a header in row 1 your data starts in row 2 so you have to add 1 to the value specified in C1 to get the correct row number for the INDEX() which has been specified as column B.
Basically the formula breaks down to SUM(''Overall Report 2012'!B2:Bx) and C1+1 provides the value of x.

[merlyn45]

Sorry, missed your first request.

The $C1+1 specifies the row number for the INDEX() function.
Because you have a header in row 1 your data starts in row 2 so you have to add 1 to the value specified in C1 to get the correct row number for the INDEX() which has been specified as column B.
Basically the formula breaks down to SUM(''Overall Report 2012'!B2:Bx) and C1+1 provides the value of x.

YES! I get it now! Thanks so much! It makes total sense..dont know why I couldnt figure that out on my own. THANKS! YOU ROCK!

[Cutter]

You're welcome.

YOU ROCK!

Only on my front porch.