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.

=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,
--
George Nicholson

Remove 'Junk' from return address.


"JJC" wrote in message
...
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.

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

"JJC" wrote:

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.

Sorry,

change the B1:B30 to B1:B100

Peo

"Peo Sjoblom" wrote:

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

"JJC" wrote:

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.

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,

"George Nicholson" wrote:


=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,
--
George Nicholson

Remove 'Junk' from return address.


"JJC" wrote in message
...
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.