rolling time period

[Guest]

In the following table in Excel; Colum A is the MMMM-YY format for dates and
column B is my data. How do I get a rolling 6 month total, rolling 12 month,
and rolling 18 month total?

January-02 2
February-03 15
March-03 0
April-03 0
May-03 0
June-03 5
July-03 6
August-03 4
September-03 0
October-03 0
November-03 2
December-03 0
January-04 0
February-04 0
March-04 0
April-04 4
May-04 5
June-04 2
July-04 0
August-04 20
September-04 10
October-04 3
November-04 5
December-04 0
January-05 0
February-05 0
March-05 0
April-05 10
May-05 0
June-05 0

I have used the following in the cell to come up with a yearly total

=SUMIF(A1:A100,">=" &DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)

but am trying to get a 6 month and 18 month total. Also I am looking for
data between Jan 04 and Dec 04 only.

Any help would be appreciated.

 

[George Nicholson]

=SUMIF(A1:A100, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()) - $C$1, 1),
B1:B100)
Inputting 6, 12 or 18 into C1 changes the calculation to a rolling 6, 12 or
18 month.

You only need to adjust months. The Date function handle any crossover
between years for you.

HTH,

 

[Guest]

First are you sure that the dates are numeric and if they are if they are
correct
if January-02 is supposed to be January 2002 then in the formula bar it
should display as 01/01/2002 or 01-01-2002 because if you type in January-02
it will default to Jan 2 2005, however if you type in any of the examples I
gave then you can use a custom format and get it to dsiplay as mmmm yy

for a 6 month it would look something like

=SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)

Regards,

Peo Sjoblom

 

[Guest]

Sorry,

change the B1:B30 to B1:B100

Peo

First are you sure that the dates are numeric and if they are if they are
correct
if January-02 is supposed to be January 2002 then in the formula bar it
should display as 01/01/2002 or 01-01-2002 because if you type in January-02
it will default to Jan 2 2005, however if you type in any of the examples I
gave then you can use a custom format and get it to dsiplay as mmmm yy

for a 6 month it would look something like

=SUMPRODUCT(--(A1:A100>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))),--(A1:A100<=TODAY()),B1:B30)

Regards,

Peo Sjoblom

 

[Guest]

How do I just get the data between 10/03-11/04. For that formula I just need
my critera to be if >=10/03 and <=11/04, but I can't figure out that peice of
it.

The rest of it worked.

Thanks,