Calculate Week, Month & Quarter sums dynamically?

PeregrinTook

Board Regular
Joined
Feb 9, 2006
Messages
157
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Feb 9, 2006
Messages
157
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Feb 9, 2006
Messages
157
:ROFLMAO:

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

:LOL:

All the best
PT
 

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top