dynamic range sum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BlackIce - 14 Jul 2006 14:43 GMT

I know how to add a dynamic range with a fixed point by doing
=SUM(INDIRECT("E14:E" & ROW()-2)),

my problem is that i need to dynamically add a range without a fixed
point. Is this possible to do with an excel formula, becuase I cannot
do it with a macro or vba code for design and implementation reasons.

so theoretically if you could do =SUM(INDIRECT(("E" & ROW-4):("E" &
ROW()-2))) (i know this formula does not work but if i could do
something like it)

Thanks

Reply to this Message

Bob Phillips - 14 Jul 2006 14:51 GMT

=SUM(INDIRECT(("E" & ROW()-4&":E"&ROW()-2)))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I know how to add a dynamic range with a fixed point by doing
> =SUM(INDIRECT("E14:E" & ROW()-2)),
[quoted text clipped - 8 lines]
>
> Thanks

Reply to this Message

BlackIce - 14 Jul 2006 19:12 GMT

Thanks that helped so much, I knew there was a way to do it just didn't
know how.

> =SUM(INDIRECT(("E" & ROW()-4&":E"&ROW()-2)))
>
[quoted text clipped - 17 lines]
> >
> > Thanks

Reply to this Message

Leo Heuser - 18 Jul 2006 08:04 GMT

> Thanks that helped so much, I knew there was a way to do it just didn't
> know how.
[quoted text clipped - 5 lines]
>>
>> Bob Phillips

Another option:

=SUM(OFFSET(E1,ROW()-ROW(E1)-4,,3))

3 being the number of cells to add.

Signature

Best regards
Leo Heuser

Followup to newsgroup only please.

Reply to this Message