Summing based on conditions

Hi all, I am trying to write a formula which sums up the cells based on the number of rows it says to go down i.e.

A B C D
3 5/5/2013 4700 28 =SUM( B3:(OFFSET(B3,C3/7-1,0)))
4 5/12/2013 4430
5 5/19/2013 4570
6 5/26/2013 3460
7 MAY 17160
8 6/2/2013 4520
9 6/9/2013 5520
10 6/16/2013 5570
11 6/23/2013 5460
12 6/30/2013 5630
13 JUNE 26700

At C3, I put multiple of 7 (# of days). Right now it says 28 means 28/7=4weeks so we need to add B3:B6. If we put 21 (3 weeks) then formula should add B3:B5. The formula for that is =SUM( B3:(OFFSET(B3,C3/7-1,0))) and its working fine. Now the problem is that if I say 35 (5 weeks) in C3, it should add B3, B4, B5, B6 & B8 but skip B7. I have written long formulas which is excluding B7 but doesn't include B8 instead. I don't want to do it in macro as the person heavily uses a huge file where macro might not be the best solution.

Any help will be much appreciated.

Thanks,
HQ

You can change the formula in D1 to this:

=SUMIF(A:A,"<"&A1+C1,B:B)-SUMIF(A:A,"<"&A1,B:B)

(compatible with XL2003, as your example file was .xls). You could use SUMIFS if you do not need backward compatibility, like this:

=SUMIFS(B:B,A:A,"<"&A1+C1,A:A,">="&A1)

Hope this helps.

Pete

Thanks Pete, It worked. You are genius!!

Have a great day.

HQ