Formula to update a YTD (year-to-date) calculation for a new

[karam]

I am looking for a simple way to update a simple calculation each month to pull the current month into an addition calculation.

ex:
J F M A M J J A S O N D (months in the year)

At the end of march I want to add March data to a YTD calculation which already pulls Jan and Feb data.

So instead of doing this manually several times, I would like to put one formula in the YTD column which will be a function of Now().

It seems as though there has to be a way to do this b'c it is such a simple concept.

THANKS!

[dimrod]

If your YTD column is column A, and your month headings are in B1:M1, then :-

- make sure the dates in the headings are all the last day of each month (can be formatted as mmm)

- put this formula in A2 and fill down
=SUMIF(B$1:M$1,"<=" &TODAY(),B2:M2)

[Yogi Anand]

On 2002-05-15 15:44, dimrod wrote:
If your YTD column is column A, and your month headings are in B1:M1, then :-

- make sure the dates in the headings are all the last day of each month (can be formatted as mmm)

- put this formula in A2 and fill down
=SUMIF(B$1:M$1,"<=" &TODAY(),B2:M2)

Hi Karam and dimrod:
thinking along dimrod's lines, the correct formula would be ...

=SUMIF(B1:M1,"<="&MONTH(TODAY()),B2:M2)
provided the entries in B1:M1 are 1,2,3, ... and 12 instead of J F M, ...

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:12 ]

[dimrod]

But don't forget the absolute refs for B1:M1

[karam]

Thanks!

This did work. However, after thinking about it. I actually want to be able to type in a month or its corresponding month no. and make the formula a function of that.

I am reporting at the end of a month info for the prior month YTD. So, in May I want the formula to pull all info for Jan - April. I also need to save documents for each month and would not want to use now() or today() because it will be updated all monthly reports for each new month.

Let me know if you have anymore thoughts.

[Corticus]

You could have your macro for generating the report cut and paste the data from the month you specify to a 'current' tab. Then have your report tab lookup its values from only the 'current' tab.

Corticus

[karam]

SUMIF(MONTH(B$15:M$15),"<="&MONTH(A1),B16:M16)

This is the formula that I am trying to use but it gives me an error message.

I am formating the dates as such and making the formula a function of MONTH().

January-02 February-02 March-02

[Yogi Anand]

On 2002-05-16 06:45, karam wrote:
SUMIF(MONTH(B$15:M$15),"<="&MONTH(A1),B16:M16)

This is the formula that I am trying to use but it gives me an error message.

I am formating the dates as such and making the formula a function of MONTH().

January-02 February-02 March-02

If you are going to have dates input into cells A15, as well as in cells B15:B16, then the following formula works:

=SUMIF(B15:M15,"<="&A15,B16:M16)

Regards!