referencing a range in a sum

Posted by Franco on December 13, 2000 2:20 PM

I'm trying to sum the values between two rows, and these values change depending on the data file I'm working on. I can get the row numbers, but I'm having trouble writing a SUM() function using/referencing these cells with these row values.
i.e.,
cell#1(row#1)
cell#2(row#2)
Sum(cell#1:cell#2)

I've tried the CELL("contents",$$) function, but I'm having trouble nesting it with SUM().
Any ideas?
Thanks for any help,

Franco

Posted by Tim Francis-Wright on December 13, 2000 7:47 PM

I'm assuming that you want to sum the cells in Column A,
but the rows will be determined elsewhere, for
example, in C1 for the start row and C2 for the
end row.

Then B1 could equal
=SUM(INDIRECT("A" & C1 & ":A" & C2))

The INDIRECT function lets you create a range
(or cell) reference by assembling a string;
the ampersands concatenate strings; and by
having only one argument, the function assumes
A1 (not R1C1) notation.

Good luck!

Posted by Franco on December 14, 2000 8:48 AM

thanks Francis