# Thread: That Sums It Up (XL97:SR2)

1. ## That Sums It Up (XL97:SR2)

Is it possible to have a formula that sums a range of cells in a column (based on a column of a ranged cell).

Example:
Cell G1 contains the range name "rngOne"

The formula should end up being something like: SUM(Cells(4,column(rngOne), Cells(10, column(rngOne))

The obvious would be to make the formula = SUM(G4:G10) but I'm trying to pass the column of the range named "rngOne" into the formula.

John <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

2. ## Re: That Sums It Up (XL97:SR2)

I think you are looking for the INDIRECT function.
If rngOne is defined as G4:G10, and in G1 you have typed the text "rngOne"
The following:
will sum the cells in G4:G10.

as well as ,

and the simpler:
=SUM(INDIRECT(G1))
Steve

3. ## Re: That Sums It Up (XL97:SR2)

Steve,

Your suggestion works as long as the formula and range is on the same sheet. What happens if the formula is on a different sheet (say Sheet2) and referencing the range name and data from Sheet1.

Unfortunately I receive nothing but #REF as the returned value. Am I missing something?

Thanks,
John

4. ## Re: That Sums It Up (XL97:SR2)

Did you try something like this:

1) you need want the first address to include the sheet
2) you want "TRUE" not "FALSE" if you always want to refer to G4 no matter what cell the formula is in. If you use false and are in A1 it will refer to G4, but if you are in B2 it will refer to H5 (4 rows down, 7 columns across, relative to the current cell). If you want to refer to a cell RELATIVE to the cell with the calc then you want FALSE.

Steve

5. ## Re: That Sums It Up (XL97:SR2)

Use this:

=SUM(INDIRECT("Sheet1!" & G1 & "4:" & G1 & "10"))

6. ## Re: That Sums It Up (XL97:SR2)

Jan,
This will only work if G1 contains the column letter of the desired column, not a range name as he originally proposed.

Steve

7. ## Re: That Sums It Up (XL97:SR2)

<img src=/S/woops.gif border=0 alt=woops width=58 height=36> Of course. Proves I wasn't paying attention while reading the first post

8. ## Re: That Sums It Up (XL97:SR2)

Try this.

Give a RangeName to the cell where you will enter the range to sum.
Then use this:=SUM(INDIRECT(CLEAN(text))). Where text = cell containing range to sum.

Regards,

Nick

#### Posting Permissions

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