Summing dynamically before given date

[hqureshi78]

Hi all, I am having list which has date, value, #of days and formula in four columns. The values are:
A B C D
5/5/2013 4700 70 43860
5/12/2013 4430
5/19/2013 4570
5/26/2013 3460
x 4290
6/2/2013 4520
6/9/2013 5520
6/16/2013 5570
6/23/2013 5460
6/30/2013 5630
x 26700
I got help yesterday in coming up with this formula which dynamically adds value based on the number visible in column C but excludes values in rows having x. The formula is =SUMIF(A:A,"<"&A3+C3,B:B)-SUMIF(A:A,"<"&A3,B:B). Now the challenge I am facing is that I need to add the values until the date given in cell (E3). Meaning that if column E3 has 5/19/2013 and we have 28 (no of days) in columns C, rather than adding 4700, 4430, 4570 and 3460. It should just add 4700, 4430 and 4570. And if C has 63 and E3 has 6/16/2013, rather than adding value between 5/5/2013 and 6/30/2013 except x. it will add value between 5/5/2013 and 6/16/2013 except value x.

Any help will be much appreciated.

Thanks,
HQ

[watersev]

hi,

Please Login or Register  to view this content.

[hqureshi78]

Thanks for the solution. You are genius.

Have a great day.

HQ