Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    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...
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    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
  •