# Thread: Using INDIRECT to specify how many cells to sum (Excel 2003)

1. ## Using INDIRECT to specify how many cells to sum (Excel 2003)

I have annual data in a horizontal format (years goiing across the top). I would like to write a formula which will sum X number of previous cells, where X is specified in a cell somewhere.

I've heard this can be done with INDIRECT, but the Excel help file on this left me confused.

I've attached an example, with the following query (The query will only make sense when you look at the example):

In Cell R27, what formula, using the INDIRECT function, sums X number of years (columns), ending with the present year (2006), when the number of years to to add is specified in cell O19?

In other words, if O19 contains a 5, how I can I, in cell R27, sum cells N26:R26?

Also -- is there a way to make this formula easily copyable across, so that, say, I could copy it to cell S27, and it would -- when O19 contains a 5 -- sum O26:S26?

Any help would be appreicated...

2. ## Re: Using INDIRECT to specify how many cells to sum (Excel 2003)

I'd use OFFSET:

=SUM(OFFSET(R26,0,1-\$O\$19,1,\$O\$19))

This formula can be copied across.

The syntax for OFFSET is OFFSET(range, row_offset, column_offset, height, width)

3. ## Re: Using INDIRECT to specify how many cells to sum (Excel 2003)

Hans saves the day again!

Promise me you'll never retire ... Promise! PROMISE!!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•