Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there a function for a 'year to date' sum
I am trying to build an automated 'year to date' column in a financial
summary worksheet but don't want to have to update the formula each month (ie in month 6, sum cells A to F, then in month 7 change the formula to sum cells A to G). Is there a function (or group of functions) which will 'lookup' the current month e.g july and add up the contents of the cells in columns from A to G (Jan to July)? The following month when I input August in a given cell the function I'm looking for will recognise this and automatically add cells A to H (Jan to Aug). Hope this makes sense. Thanks in anticipation. |
#2
|
|||
|
|||
Assuming the row you want to sum is the 10th row
=SUM(OFFSET(A10,0,0,1,MONTH(TODAY()))) "Mal" wrote: I am trying to build an automated 'year to date' column in a financial summary worksheet but don't want to have to update the formula each month (ie in month 6, sum cells A to F, then in month 7 change the formula to sum cells A to G). Is there a function (or group of functions) which will 'lookup' the current month e.g july and add up the contents of the cells in columns from A to G (Jan to July)? The following month when I input August in a given cell the function I'm looking for will recognise this and automatically add cells A to H (Jan to Aug). Hope this makes sense. Thanks in anticipation. |
#3
|
|||
|
|||
"Mal" wrote in message
... I am trying to build an automated 'year to date' column in a financial summary worksheet but don't want to have to update the formula each month (ie in month 6, sum cells A to F, then in month 7 change the formula to sum cells A to G). If you have a sheet with calendarised figures (ie each month in a separate column - and it's a very good analysis tool to do that anyway) and a total in rows for the twelve months, then the YTD is done automatically, because all future months are blank! No need for any "automation"! -- Gordon Burgess-Parker Systems and Management Accounting www.gbpcomputing.co.uk |
#4
|
|||
|
|||
Hello Mal: Cell M1 is todays date ( 7/20/2005 ). =SUM(INDIRECT("A:"&CHOOSE(MONTH(M1),"A","B","C","D ","E","F","G","H","I","J","K","L"))) Matt -- Flintstone ------------------------------------------------------------------------ Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310 View this thread: http://www.excelforum.com/showthread...hreadid=388435 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to caculate date | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Day number of given date in a year | Excel Worksheet Functions | |||
Date function question | Excel Discussion (Misc queries) | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions |