Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Sum a Range of Cells based on the date
Hello,
I have a table with each column titled by the month, and in row 1 I have a range of data. I would like to have a formula that automatically sums up the range up to the current month without having to enter in a new formula any month. Is this possible? I imagine it would require the TODAY() function but after that I am lost. Example: Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0 Today I would want to add up the Nov-Jan colums to get (5), but next month when I open the same spreadsheet I would like that same formula to add up the Nov-Feb columns to get (7) Thanks! Joe |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Sum a Range of Cells based on the date
There are a few approaches
Using your posted data in A1:H2 and If the first row of data contains actual dates that are the 1st of the month Try this: A3: =SUM(A2:INDEX(A2:H2,1,MATCH(TODAY(),A1:H1,1))) Is that something you can work with? Or.... if your data has a different structure, please share it. *********** Regards, Ron XL2002, WinXP "jcpotwor" wrote: Hello, I have a table with each column titled by the month, and in row 1 I have a range of data. I would like to have a formula that automatically sums up the range up to the current month without having to enter in a new formula any month. Is this possible? I imagine it would require the TODAY() function but after that I am lost. Example: Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0 Today I would want to add up the Nov-Jan colums to get (5), but next month when I open the same spreadsheet I would like that same formula to add up the Nov-Feb columns to get (7) Thanks! Joe |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Sum a Range of Cells based on the date
Hi Ron,
It works perfectly - THANK YOU SO MUCH !!!!! "Ron Coderre" wrote: There are a few approaches Using your posted data in A1:H2 and If the first row of data contains actual dates that are the 1st of the month Try this: A3: =SUM(A2:INDEX(A2:H2,1,MATCH(TODAY(),A1:H1,1))) Is that something you can work with? Or.... if your data has a different structure, please share it. *********** Regards, Ron XL2002, WinXP "jcpotwor" wrote: Hello, I have a table with each column titled by the month, and in row 1 I have a range of data. I would like to have a formula that automatically sums up the range up to the current month without having to enter in a new formula any month. Is this possible? I imagine it would require the TODAY() function but after that I am lost. Example: Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0 Today I would want to add up the Nov-Jan colums to get (5), but next month when I open the same spreadsheet I would like that same formula to add up the Nov-Feb columns to get (7) Thanks! Joe |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Sum a Range of Cells based on the date
Hi,
Another way: =SUMPRODUCT(--(A1:H1<=TODAY()),A2:H2) Cheers! Jean-Guy "jcpotwor" wrote: Hello, I have a table with each column titled by the month, and in row 1 I have a range of data. I would like to have a formula that automatically sums up the range up to the current month without having to enter in a new formula any month. Is this possible? I imagine it would require the TODAY() function but after that I am lost. Example: Nov-06 Dec-06 Jan-07 Feb-07 Mar-07 Apr-07 May-07 Jun-07 1.0 2.0 2.0 2.0 1.0 1.0 1.0 2.0 Today I would want to add up the Nov-Jan colums to get (5), but next month when I open the same spreadsheet I would like that same formula to add up the Nov-Feb columns to get (7) Thanks! Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges using non-contiguous cells and dependent on a cell value | Excel Worksheet Functions | |||
Return info based on Date Range | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
Auto populate cells based on 2 cells division. | Excel Discussion (Misc queries) |