[SOLVED] Is there a function for a 'year to date' sum

[Mal]

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.

[Duke Carey]

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.

[Gordon]

"Mal" <[email protected]> wrote in message
news:[email protected]...
>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

[Flintstone]

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