|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 14th, 2009, 10:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. 
|
|
#2
May 14th, 2009, 11:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#3
May 14th, 2009, 11:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#4
May 16th, 2009, 12:17 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#5
May 16th, 2009, 03:54 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
