How can I find the greatest possible sum within 12 months? A newbie...

[Guest]

Just when I thought I had finally solved the problem, I noticed one
more thing:
as the numbers come to an end, the formula does not work anymore. The
result is true up to the point that there is a corresponding value with
a date 1 year in advance - if there is not, it is simply not counted
in.

Thus, if my last sum is, say, 10$ on the 10 Sept 2006, then the
formula, pasted in as you suggested, will only show a total of 10$ -
eventhough there are values preceeding it in the past 12 months, and it
should show a maximum for the period 10 Sept 2005 to 10 Sept 2006 (and,
instead, it is showing 10 Sept 2006 to 10 Sept 2007... which cannot
work of course, as there are no values for that time period). It works
perfectly fine up to a date that has a value in 12 months - once that
is gone, it just simply decreases, instead of looking backwards to
count those 12 months.

Any suggestions? If I had a clearer explanation of the formula I could
probably work it out myself, but unfortunately I am not such an excel
expert. Basically I would need something that takes into account the
fact to switch to look backwards for 12 months and not forwards, once
the end sum date is reached, or the same formula as before but in
reverse, taking the sums and adding the last 12 months, instead of the
12 months ahead, I can figure out the rest myself...

 

[Roger Govier]

Hi

The formula I posted dealt with fixed dates for any period of time that
you entered where start sate was in column F and end date in column G.
You therefore have control over what time periods you want the data
summed.

If you are now saying that you want to know the total amount for every
row, for the period of 12 months ending on the date for that particular
transaction, then in cell C2 of the file I sent you, enter the following
formula and copy down

=SUMPRODUCT(($B$2:$B$15>=DATE(YEAR(B2)-1,MONTH(B2),DAY(B2)+1))
*($B$2:$B$15<=B2)*($A$2:$A$15))

 

[Guest]

Thanks!