12MMT - How ?

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Rob,

An alternative would be to use a Pivot Table. Add a helper column - say
labelled "Period" with a formula similar to:

=IF(AND(A2>=Sheet2!$A$1,A2<Sheet2!$B$1),Sheet2!$B$1,"")

This column may be hidden afterwards if necessary.

When you construct the Pivot Table drag "Period" into the "Row".
Double-click on the "Period" button and in the "Hide Items" box clcik on the
enpty line above the date. then click OK. This will hide the rows with the
empty string, (or whatever else you use n place of the empty string). Next
drag "Employee Number" and what ever else you want into "Column" and finally
"Hours into the "Data" and finish constructing the Pivot Table as you wish.

The disadvantage of Pivot tables is that they are not automatic and have to
be refreshed but for large amounts od data they are very fast.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

Sumproduct will work just fine. When you enter the "search" date and you
enter it as Feb 07 the true underlying value of the cell is Feb 7 2007 even
though you may have it formatted as mmm yy. However, this shouldn't be
problem since your data dates are entered as 1 mmm yy. Ambiguous dates are a
bad idea and can cause unexpected problems. Feb 07 is an ambiguous date.

For the 12 month sum:

A1 = Feb 07

=SUMPRODUCT(--(Sheet1!A1:A100>DATE(YEAR(A1)-1,MONTH(A1),1)),--(Sheet1!A1:A100<=A1),Sheet1!B1:B100)

For the 12 month average:

I don't know how much data you have or when it starts or if you might have
empty cells for certain months but assuming there is always enough data and
no empty cells all you have to do is divide the sum by 12. However, I
wouldn't trust all that so here's an average formula:

=SUMPRODUCT(--(Sheet1!A1:A100>DATE(YEAR(A1)-1,MONTH(A1),1)),--(Sheet1!A1:A100<=A1),Sheet1!B1:B100)/SUMPRODUCT(--(Sheet1!A1:A100>DATE(YEAR(A1)-1,MONTH(A1),1)),--(Sheet1!A1:A100<=A1))

 

[Rob L]

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

 

[Rob L]

I'm not sure that SUMPRODUCT is what I want (I've tried your formula without
it doing what I wanted).

I actually have a large sheet with various columns of data (hours, Employee
Numbers, Lost Time Injuries etc). Column 1 contains months (01 mm yy
formatted as mmm yy), in sequence.

I need to be able to fill a table on Sheet 2 with various sums of the data,
based on a month that I put in cell A1 on Sheet 2. So if I put Feb 07 in A1,
I need the sum of the hours, employee numbers, and LTI's for the period Mar
06 - Feb 07, to go into cells B2, B3, and B4. If I change A1 to Jun 07, then
the sums change to the sums for Jul 06 - Jun 07.

The formula that I use to get the "Start Month" is =EOMonth(A1,-12)+1 (which
works OK), but how do I get the Total (or Average) of the cells between
these two dates, for the required column ? My thoughts turn towards an Array
formula, but I'm not that proficient with them....

Thanks for any help.

Rob L

 

[Rob L]

Thanks Guys - once I got the correct ranges in - worked a treat.

RobL