Define variable range input for SUM() function
 

Hi,
I need to be able to sum a range that will vary in size depending upon the
month. For instance, if the month is march, then I would like the SUM
function to pick-up the values in the Jan, Feb, and March columns. However,
if the month is June, I need it to sum the values for all 6 months, etc. So,
if the month is March, the formula would look like =SUM(B2:B4), but June
should look like =SUM(B2:B7). Is there any way to make the range "grow"
(i.e., from B4 to B7) automatically? I'd like to have either text or a
number representing the month in a cell that I could change which would
control the range covered by the SUM() formula. Thanks in advance.

Define variable range input for SUM() function
 

I'm still working through this... but why doesn't the following formula work?
=SUM(INDIRECT(INDEX(F6:I7,1,1):INDEX(F6:I7,1,F4))) ... where F4 equals an
integer representing a month/column in the range?

Define variable range input for SUM() function
 

Try it like this:

=SUM(F6:INDEX(F6:I6,F4))

If F4 is an empty cell the entire range will be calculated.

--
Biff
Microsoft Excel MVP


"JeffC" wrote in message
...
I'm still working through this... but why doesn't the following formula
work?
=SUM(INDIRECT(INDEX(F6:I7,1,1):INDEX(F6:I7,1,F4))) ... where F4 equals an
integer representing a month/column in the range?

Define variable range input for SUM() function
 

Thanks! That did it!

"T. Valko" wrote:

Try it like this:

=SUM(F6:INDEX(F6:I6,F4))

If F4 is an empty cell the entire range will be calculated.

--
Biff
Microsoft Excel MVP


"JeffC" wrote in message
...
I'm still working through this... but why doesn't the following formula
work?
=SUM(INDIRECT(INDEX(F6:I7,1,1):INDEX(F6:I7,1,F4))) ... where F4 equals an
integer representing a month/column in the range?

Define variable range input for SUM() function
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JeffC" wrote in message
...
Thanks! That did it!

"T. Valko" wrote:

Try it like this:

=SUM(F6:INDEX(F6:I6,F4))

If F4 is an empty cell the entire range will be calculated.

--
Biff
Microsoft Excel MVP


"JeffC" wrote in message
...
I'm still working through this... but why doesn't the following formula
work?
=SUM(INDIRECT(INDEX(F6:I7,1,1):INDEX(F6:I7,1,F4))) ... where F4 equals
an
integer representing a month/column in the range?