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.

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.

"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

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