Incrementing Functions by Column

**thegrubixcube** · 02-12-2010, 11:22 AM

Hi all,

I'm having trouble incrementing a function by columns instead of rows.

On one sheet I have my data in columns that I am referencing. On another I have my formula which is:
=SUM(Data!B5:B78)

I need to drag this formula down multiple rows, each time increasing the reference column by one, so that for the next few rows I get =SUM(Data!C5:C78), =SUM(Data!D5:D78)...etc.

When I drag it down, it increments by rows as it should. When I change the formula to:
=SUM(Data!B$5:B$78)
to lock the rows and try to drag down, the exact formula just copies to the rows below it.

Thanks for your help!

I am using Excel 2003.

**DonkeyOte** · 02-12-2010, 11:24 AM

You don't stipulate how far but in general...

Assume first formula is in A2:

=SUM(INDEX(Data!$5:$78,0,ROWS(A$1:A2)))
copied down

A2 will sum B5:B78, A3 C5:C78 and so on...