SumIf statement: incrementing

[Kesbutler]

I have the following formula:
=SUMIF('weekly dates'!A1:AZ1,">"&A5,Details!L7:BK7)

Weekly dates is a static sheet, &A5 is the current date and Details!L7:BK7
are cells that are calculated weekly on a sheet different from where this
formula is used.

There are 4 weeks per period and this report is done weekly. I need the
SUMIF to be just as it is for week 1, but for week 2, I need to include weeks
1&2, week 3 to include 1-3 and week 4 to include 1-4. Then it starts over
after week 4. I can do by changing the formula weekly in the following way.
=SUMIF('weekly dates'!A1:AZ1,">"&A5-8,Details!L7:BK7) week2
=SUMIF('weekly dates'!A1:AZ1,">"&A5-16,Details!L7:BK7) week3
=SUMIF('weekly dates'!A1:AZ1,">"&A5-24,Details!L7:BK7) week4

I know there has to be a cleaner and more correct way of doing this. Any
ideas?

 

[Bernard Liengme]

I put dates in column G and numbers to sum in column H
In A5 I have the "current date"
I used this formula
=SUMIF(G1:G31,">"&A5-IF(DAY(A5)<8,8,IF(DAY(A5)<14,16,24)),H1:H31)
to try to duplicate your three options.

DAY(A5) returns the calendar day and I check if was less than 14, less than
14 to compute how many days to subtract from A5.
You will need to work on it - 8 days in a week had me confused
best wishes

 

[Bernard Liengme]

I put dates in column G and numbers to sum in column H
In A5 I have the "current date"
I used this formula
=SUMIF(G1:G31,">"&A5-IF(DAY(A5)<8,8,IF(DAY(A5)<14,16,24)),H1:H31)
to try to duplicate your three options.

DAY(A5) returns the calendar day and I check if was less than 14, less than
14 to compute how many days to subtract from A5.
You will need to work on it - 8 days in a week had me confused
best wishes

 

[Kesbutler]

Thanks but this isn't exactly what I need. I need the formula to only
calculate the weeks in that current period. Your formula grabs the weeks
previous based on the date.

Each period has 4 weeks and I need to track each week within each period and
increment as the period progresses.

Week 1 = only calculates week 1
Week 2 = calculates week 1 & week2
Week 3 = calculates weeks 1,2 and 3
Week 4 = calcualtes weeks 1,2,3 and 4.

Then after week 4 it would start over again with week 1 as it would be a new
period.

I guess I'll keep plugging away until I get it figured out.

Thanks

 

[Kesbutler]

Thanks but this isn't exactly what I need. I need the formula to only
calculate the weeks in that current period. Your formula grabs the weeks
previous based on the date.

Each period has 4 weeks and I need to track each week within each period and
increment as the period progresses.

Week 1 = only calculates week 1
Week 2 = calculates week 1 & week2
Week 3 = calculates weeks 1,2 and 3
Week 4 = calcualtes weeks 1,2,3 and 4.

Then after week 4 it would start over again with week 1 as it would be a new
period.

I guess I'll keep plugging away until I get it figured out.

Thanks