Variable sum

allene · Apr 2nd, 2002, 10:36 AM

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. · Apr 2nd, 2002, 10:46 AM

=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 · Apr 2nd, 2002, 10:51 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 10:36 AM	#1
allene New Member Join Date: Apr 2002 Posts: 1	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

Apr 2nd, 2002, 10:46 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=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 ]

Apr 2nd, 2002, 10:51 AM	#3
Russell Hauf MrExcel MVP Join Date: Feb 2002 Location: Portland, OR USA Posts: 1,374	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