Variable sum

[allene]

I want to sum up a range of cells but have the number of cells be a variable defined by another cell. It would be nice if the following worked, for example
=sum(b18:b(18-a18)), but it doesn't. This would sum up all the cells from b18 back for a18 cells

 

[Mark W.]

=SUM(B18:INDIRECT("B"&18-A18))

or

=SUM(B18:OFFSET(B18,-A18,))
This message was edited by Mark W. on 2002-04-02 09:48

 

[Russell Hauf]

If you want to sum the number of cells in cell A18, then you will need to add one:

=SUM(B18:INDIRECT("B"&18-A18+1))

For example: you have 4 in cell A18, and you want to sum only the last 4 cells. 18-4=14, but the range B18:B14 is actually 5 cells (14,15,16,17,18).

HTH,

Russell