dynamic range sum

B

BlackIce

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

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

Leo Heuser

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


Another option:

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

3 being the number of cells to add.
 
Top