Re: 12MMT - How ?

Giganews Newsgroups
Subject: Re: 12MMT - How ?
Posted by:  Sandy Mann (sandyman…@mailinator.com)
Date: Sat, 4 Aug 2007

With your data in Sheet1 starting from A1 and the start date you want in
Sheet2 A1 put:

=DATE(YEAR(A1),MONTH(A1)+12,DAY(A1))

In B1

Then use the formula:

=SUMPRODUCT((Sheet1!A2:A106>=A1)*(Sheet1!A2:A106<B1)*Sheet1!C2:C106)

Adjust to suit your needs.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandyman…@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"Rob L" <robertlepperta…@thisoptusnet.outcom.au> wrote in message
news:46b45235$0$31115$afc38c…@news.optusnet.com.au...
>I have a table of data
>
> Month Hours
> Jan 06    2005
> Feb 06    1750
> Mar 06    3520
> ....
> Mar 07    2300
>
> I have a table on a separate sheet. I want to be able to put (say) Mar 07
> in cell A1, and have the 12 month moving total (total from Mar 07 to April
> 06) in cell A2. If I change A1 to Feb 07, then the 12MMT range is from Feb
> 07 to Mar 06, and I want the total to change to reflect this.
>
> Can someone help with a formula please
>
> Thanks,
>
> Rob L

Replies

In response to

12MMT - How ? posted by Rob L on Sat, 4 Aug 2007