Assign the cell address with a function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

T.Mad - 09 Feb 2007 01:16 GMT

Hi all,
I want to calculate the sum of a column, for example the sum from A1 to A10
which is the function =sum(A1:A10). But instead of A10 which identifies the
last cell of the sum, I want the row number to be taken from a function that
returns an integer number. Thus, the last cell of the sum in the row may be
A10 or A12 etc according to the integer which my function will return. Can
anybody help me? If I should program it with macros how can I do that? Thanks
in advance.

Reply to this Message

Ron Rosenfeld - 09 Feb 2007 01:34 GMT

>Hi all,
>I want to calculate the sum of a column, for example the sum from A1 to A10
[quoted text clipped - 4 lines]
>anybody help me? If I should program it with macros how can I do that? Thanks
>in advance.

You could use a formula of the type:

=SUM(OFFSET(A1,0,0,last_cell_row))

You'll have to adjust last_cell_row depending on the row of the first cell.

So if you don't start at A1, then the formula might have to be:

=SUM(OFFSET(A1,0,0,last_cell_row - ROW(first_cell_address)+1))

--ron

Reply to this Message

T.Mad - 09 Feb 2007 01:52 GMT

Thx ron U been very helpful!!!!

> >Hi all,
> >I want to calculate the sum of a column, for example the sum from A1 to A10
[quoted text clipped - 16 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 09 Feb 2007 02:38 GMT

>Thx ron U been very helpful!!!!
>
[quoted text clipped - 18 lines]
>>
>> --ron

You're welcome. Glad to help.
--ron

Reply to this Message

Teethless mama - 09 Feb 2007 02:36 GMT

=SUM(B2:INDIRECT("B"&ROWS(B1:B100)))

> Hi all,
> I want to calculate the sum of a column, for example the sum from A1 to A10
[quoted text clipped - 4 lines]
> anybody help me? If I should program it with macros how can I do that? Thanks
> in advance.

Reply to this Message

T. Valko - 09 Feb 2007 03:21 GMT

Try this:

D1 = a formula that returns an integer row number (per your description)

=SUM(A1:INDEX(A:A,D1))

If D1 is empty the formula will calculate the entire range.

Biff

> Hi all,
> I want to calculate the sum of a column, for example the sum from A1 to
[quoted text clipped - 9 lines]
> Thanks
> in advance.

Reply to this Message