Calculate Week, Month & Quarter sums dynamically?

[PeregrinTook]

Here's one for you creative formulae-type people!

I have a table of 10 rows for salespeople by 53 rows for each week-end date (always a Friday, header is format 29/09/2006) of the calendar year.

At the end of the table, after the last column, I would like 3 columns called "Last Week", "Month to Last Week" and "Rolling Quarter to Last Week"

The data will always be updated on a Monday, so I would like these new columns to update immediately, and preferably with formulae rather than vba if possible...

So at any given time:

"Last Week" should show what the values were for each salesperson in the last calendar week. So if opened today, "Last Week" should show values from w/e Friday 29th Sept.

"Month to Last Week" should show the sum of each person's sales for all weeks in the month of last week's update. So if opened today, even although we're now in October, "Month to Date" should show the sum of values from w/e Friday 1st Sept to w/e Friday 29th Sept because the last week of data is still September.

"Rolling Quarter to Last Week" should show the sum of each person's sales for the thirteen weeks up to and including last week. So if opened today, "Rolling Quarter to Last Week" should show the sum of values from w/e Friday 7th July to w/e Friday 29th Sept.

Hope all that makes sense, any help gratefully received!

Thanks
PeregrinTook

 

[Andrew Poulsom]

Last Week:

=INDEX(B2:BB2,1,COUNT(B2:BB2))

Month to Last Week:

=SUMPRODUCT(--(MONTH(B1:BB1)=MONTH(INDEX(B1:BB1,1,COUNT(B2:BB2)))),B2:BB2)

Rolling Quarter to Last Week:

=SUM(INDEX(B2:BB2,1,MAX(1,COUNT(B2:BB2)-12)):INDEX(B2:BB2,1,COUNT(B2:BB2)))

 

[PeregrinTook]

Andrew,

That is beautiful, absolutely beautiful I tell ya!

Many thanks
PT

 

[PeregrinTook]

Andrew,

Now that I can see what's possible with your formulae I wonder if it could also handle the date columns being expanded out beyond one year?

I tried doing just that (and amending your formulae accordingly), and it seems to accommodate the "Last Week" and "Rolling Quarter" but has a problem handling "Month to Last Week" - I think because it's just taking the month value, then finds that month for the first time in the first year, so just uses that.

Could you pls tell me how to modify that formulae for "Month to Last Week" to also look at the year?

Many thanks
PT

 

[Andrew Poulsom]

I think:

=SUMPRODUCT(--(MONTH(B1:BB1)=MONTH(INDEX(B1:BB1,1,COUNT(B2:BB2)))),--(YEAR(B1:BB1)=YEAR(INDEX(B1:BB1,1,COUNT(B2:BB2)))),B2:BB2)

 

[PeregrinTook]

You are just too good at this Andrew, sincerely - much appreciated. I could never have come up with formulae like that!



All the best
PT