Sumif based on rolling time period

[Guest]

I need to sum the number of units used per 24-month period based on the date the unit is used. The first two columns represent the date of use and the number of units; the third column is what I need to calculate by summing the numbers in the second column for only those rows in which the date is within 24 months of the date in the current row

01/02/2000 1
02/02/2000 4
03/02/2000 3
04/02/2000 3 11
12/15/2001 2 13
01/05/2002 2 14 [eliminates the 01/02/2000 1 and adds the 01/05/2002 2
02/05/2002 2 11 [eliminates the 02/02/2000 5 and adds the 02/05/2002 2
03/05/2002 1 4 [eliminates the 03/02/2000 8 and adds the 03/05/2002 1

Dates can vary in frequency

Ideas? Thanks!

 

[hpwalters]

-----Original Message-----
I need to sum the number of units used per 24-month

period based on the date the unit is used. The first two
columns represent the date of use and the number of
units; the third column is what I need to calculate by
summing the numbers in the second column for only those
rows in which the date is within 24 months of the date in
the current row:

01/02/2000 1 1
02/02/2000 4 5
03/02/2000 3 8
04/02/2000 3 11
12/15/2001 2 13
01/05/2002 2 14 [eliminates the 01/02/2000 1 and adds the 01/05/2002 2]
02/05/2002 2 11 [eliminates the 02/02/2000 5 and adds the 02/05/2002 2]
03/05/2002 1 4 [eliminates the 03/02/2000 8 and adds the 03/05/2002 1]

Dates can vary in frequency.

Ideas? Thanks!
.
Here's what I cam up with but it does not produce quite

the same results as your example

Date Max Date Incidence 24Mnth Check
1/2/2000 1/1/2002 1 1 1
2/2/2000 2/1/2002 4 5 5
3/2/2000 3/2/2002 3 8 8
4/2/2000 4/2/2002 3 11 11
12/15/2001 12/15/2003 2 13 13
1/5/2002 1/5/2004 2 14 14
2/5/2002 2/5/2004 2 11 12
3/5/2002 3/4/2004 1 4 10

Of course Max Date is new and was obtained by adding 730
days to Date. The Check column is my formula and I use
=SUMIF($B$2:B2,CONCATENATE(">",VALUE(A2)),$C$2:C2)for the
first cell. If you copy this down the column, the
unanchored cells will change. This formula assumes that
the dates are sorted in ascending order. The concatenate
and value functions are used to create a term that can be
evaluated in the sumif statement.

Hope this helps

Regards,
HPW

 

[Dallymo]

-----Original Message-----
I need to sum the number of units used per 24-month

period based on the date the unit is used. The first two
columns represent the date of use and the number of
units; the third column is what I need to calculate by
summing the numbers in the second column for only those
rows in which the date is within 24 months of the date in
the current row:

01/02/2000 1 1
02/02/2000 4 5
03/02/2000 3 8
04/02/2000 3 11
12/15/2001 2 13
01/05/2002 2 14 [eliminates the 01/02/2000 1 and adds the 01/05/2002 2]
02/05/2002 2 11 [eliminates the 02/02/2000 5 and adds the 02/05/2002 2]
03/05/2002 1 4 [eliminates the 03/02/2000 8 and adds the 03/05/2002 1]

Dates can vary in frequency.

Ideas? Thanks!
.
Here's what I cam up with but it does not produce quite

the same results as your example

Date Max Date Incidence 24Mnth Check
1/2/2000 1/1/2002 1 1 1
2/2/2000 2/1/2002 4 5 5
3/2/2000 3/2/2002 3 8 8
4/2/2000 4/2/2002 3 11 11
12/15/2001 12/15/2003 2 13 13
1/5/2002 1/5/2004 2 14 14
2/5/2002 2/5/2004 2 11 12
3/5/2002 3/4/2004 1 4 10

Of course Max Date is new and was obtained by adding 730
days to Date. The Check column is my formula and I use
=SUMIF($B$2:B2,CONCATENATE(">",VALUE(A2)),$C$2:C2)for the
first cell. If you copy this down the column, the
unanchored cells will change. This formula assumes that
the dates are sorted in ascending order. The concatenate
and value functions are used to create a term that can be
evaluated in the sumif statement.

Hope this helps

Regards,
HPW

Thanks! Your formula gives me correct results (unlike my own
arithmetically-challenged skills in dummying up the sample data!). I
appreciate the help!

--Monica

 

[JerryG]

I think the math in your example may be off a bit, but try
this array formula and see if it gives you the results you
are looking for:

=SUM((A$1:A$8<=VALUE(A1))*B$1:B$8)-SUM((A$1:A$8<VALUE(A1)-
730)*B$1:B$8)

The 730 is for the number of days in 24 months. You can
use either the < or <= in the second half of the formula
depending on whether or not you need to include the units
used exactly 24 months prior to the date in question.
Remember to use Ctrl+Shift+Enter to enter the formula and
the {} brackets will be added automatically indicating the
formula is an array formula. If you use just the Enter
key and don't see the {} brackets added to the beginning
and end of your formula, you will not get the results you
are looking for. In general, array formulas are much more
powerful than the SUMIF.

Hope this helps!

Jerry

-----Original Message-----
I need to sum the number of units used per 24-month

period based on the date the unit is used. The first two
columns represent the date of use and the number of units;
the third column is what I need to calculate by summing
the numbers in the second column for only those rows in
which the date is within 24 months of the date in the
current row:

01/02/2000 1 1
02/02/2000 4 5
03/02/2000 3 8
04/02/2000 3 11
12/15/2001 2 13
01/05/2002 2 14 [eliminates the 01/02/2000 1 and adds the 01/05/2002 2]
02/05/2002 2 11 [eliminates the 02/02/2000 5 and adds the 02/05/2002 2]
03/05/2002 1 4 [eliminates the 03/02/2000 8 and adds the 03/05/2002 1]

Dates can vary in frequency.

Ideas? Thanks!
.