SumProduct or Countif in a Date Range

Hi

Can you please explain how you got those numbers in column D? It seems to be a progressive sum that adds when the date difference is < 180 days, and subtracts when the date difference is >= 180 days, with the exception of the last item (row 7) which seems to add when the date difference is 180, even though the previous 2 rows have been a subtraction.

rylo

Here is the formula for C:
=IF(a2="","",IF(DATE(YEAR(a2),MONTH(a2),DAY(a2)-180)<a2,a2,DATE(YEAR(a2),MONTH(a2),DAY(a2)-180)))
[note] (C) is the date range to look back to see if the prior dates in (a) should be included.
Basically, when the event in (a) occurs, what is the sum (d) of the values (b) that have occurred in the previous 6 months.
A5) 1/4/2014 180 days look back is c5) 7/8/13, so A2 and A3 would not be included and B2 & B3 would not be added to D5.
What I am trying to figure out is what formula to use in D.
I really don't need (C), but I'm using it to see what dates in A should be added.
If prior dates in A are greater than 180 days, don't add the vaules in B.
If prior dates in A are less than 180 days, add the values in B.

Hope this helps explain it better.
Thanks for your help.
Rick

Hi

Can you please attach an example file that has your formulas etc. Makes things easier to understand.

rylo

Hi rylo,
sorry for the confusion.
Here is the simple version.



6 dates that occur (or it could be a dozen dates).

If these dates are within a 6 month time-frame, add the vaules of those dates.

Example: A6 occurred on 1/4/14. (I know it's a future date)
How many dates occurred within the previous 6 months? only 1: A5. A4 and A3 are outside the 180 days - 7/8/13.

D6 then adds B6 and B5 = 1.5. What criteria/formula do I use to make this work?

Really appreciate your help.
Thanks
Rick

Hi

The way i interpret this is that if the date in column A is within C1 number of days of A3, then you add the value in B to the rolling total, otherwise you subtract it from the rolling total.

If so then
D3: =b3
D4: =IF(A4<=$A$3+$C$1,D3+B4,D3-B4). Copy this down to D8.

rylo

Hi Rylo,
Close and I think you are on the right track. Unfortuantely, D8 is equal to Zero (0), when it should equal 2. I've reattached the sheet showing the excel answers. I need to add the previous 6 months totals and I think you are future dating by adding the 180 days to the date: $A$3+$C$1.
The problem I am having is determining the formula to add the totals in a "rolling" 6 months period.
A1) Date B-Value D-Sum
A2) 06/30/2013 0.5 = 0.5 [A2]
A3) 07/02/2013 0.5 = 1.0 [A3 & A2]
A4) 08/31/2013 1.0 = 2.0 [A4 & A3 & A2]
A5) 01/04/2014 0.5 = 1.5 [A5 & A4]
A6) 05/10/2014 0.5 = 1.0 [A6 & A5]
A7) 05/28/2014 1.0 = 2.0 [A7 & A6 & A5]

The Dates in A are random. The values in B could be anywhere from 0.5 to 5.0. Using the 180 days to lookback, what are the total vaules of those prior dates.

Hope this helps to explain what I am trying to do.
Teally appreciate your help.
Rick

Hi

Sorry, but it is still not clear. I'm using the date from A3 as a fixed point, and adding the value in C1 from that date, to determine the relevant date range to be evaluated. I'm doing that because of the formula you have put into column C. Your formula in column C takes 180 from the date in column A, and if that is less than the date in A3, then add the value. That is effectively the same as the date in column A being <= A3 + 180.

Doesn't matter which one of those approaches you take, the result of A7 will be subtracted from the rolling total, not added to it.

Can you explain exactly why you thing row 7 should be added not subtracted. What is the business rule as it doesn't come within 180 days of A3. Should A3 not be a fixed reference point.

rylo

Hi Rylo,
Thanks for being patient with me and trying to understand. Let me try another way. For example, would it help if said that today is "5/28/14" (A7) and I wanted to know the vaule of the prior six month occurances? In other words, if I changed the dates in A2 to A6 as past dates and A7 was today, "5/17/13", what would the total of the prior 6 months values be in "C7". My next entry in A8 could be some future date (i.e. 6 days or 6 weeks later), and at some point (180 days), those prior values would not be added. Does that help answer your last question as to why I think it should be added and not subtracted? By definition the calculation is each cell of "C" is a rolling 180 day lookback at the prior dates in "A" to determine what vaules in "B" are to be added. Yes, A2 would be the fixed point, since it's the first occurance, but it is possible that it wouldn't be added as time (180 days) goes by while additional dates occurs.
How did I do? Does this make better sense? I am sorry this hasn't been easy to describe and do appreicate your persistence with me.
Thank you
Rick