That Sums It Up (XL97:SR2)

Topic

New Reply

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

Reply | Quote

Replies

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:
=SUM(INDIRECT((ADDRESS(4,COLUMN(rngone)))&”:”&ADDRESS(10,COLUMN(rngone))))
will sum the cells in G4:G10.

as well as ,
=SUM(INDIRECT(ADDRESS(4,COLUMN(INDIRECT(G1)))&”:”&ADDRESS(10,COLUMN(INDIRECT(G1)))))

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

Reply | Quote

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.

One would think that “ADDRESS(4,COLUMM(rngone))” should be replaced by “ADDRESS(4,COLUMM(rngone),2,FALSE,”Sheet1″))”

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

Thanks,
John

Reply | Quote

Did you try something like this:

=SUM(INDIRECT(ADDRESS(4,COLUMN(rngOne),1,TRUE,”Sheet2″)&”:”&ADDRESS(10,COLUMN(rngOne))))

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

Reply | Quote

Use this:

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

Reply | Quote

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

Steve

Reply | Quote

Of course. Proves I wasn’t paying attention while reading the first post

Reply | Quote

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

Reply | Quote