Modify Monthly sales by week (7 days) or correct the formula

geo Jul

New Member
 Dear All Experts and Geniuses​ May I kindly request your help I need the two formulas in Cell G10 and Cell G11 to read from Cell G8 instead of Cell G9​ Cell G10 =SUM(FILTER(\$D\$6:\$D\$371,(\$B\$6:\$B\$371>G9-IF(MOD(DAY(G9),7)=0,7,MOD(DAY(G9),7)))*(\$B\$6:\$B\$371<=G9)))​ Cell G11 =SUMPRODUCT((\$B\$6:\$B\$371 > G9 - IF(MOD(DAY(G9), 7) = 0, 7, MOD(DAY(G9), 7))) * (\$B\$6:\$B\$371 <= G9) * (\$D\$6:\$D\$371))​ Any assistance is greatly appreciated.​ Regards​ P.S. At home I use 365 Microsoft at the office some colleagues and myself use Excel 2016​ The formula in Cell G10 was a great help from expert AliGW​

Attachments

• Book2.xlsx
54.8 KB · Views: 2
I only do 365 formulae using defined names so my formula would start out as
Code:
``````= LET(
endWeek,   DATEVALUE(TEXTAFTER(@period, "-")),
criterion, (date>endWeek-IF(MOD(DAY(endWeek),7)=0,7,MOD(DAY(endWeek),7)))*(date<=endWeek),
selected,  FILTER(sales, criterion),
result,    SUM(selected),
result
)``````
Basically all that I tried to do was unravel the mess that is created by using text to describe the weekly periods. It is very much a 365 solution, so not much use in legacy (that is a polite way of saying 'obsolete') Excel at work.

Attachments

• Book2.xlsx
55.2 KB · Views: 3
Another formula that sums over weeks may be derived using WRAPCOLS
Code:
``````= LET(
monthSales, FILTER(sales, MONTH(date)=@month),
BYCOL(WRAPCOLS(monthSales, 7,""), SUM)
)``````

Attachments

• Book2.xlsx
56.3 KB · Views: 1
I only do 365 formulae using defined names so my formula would start out as
Code:
``````= LET(
endWeek,   DATEVALUE(TEXTAFTER(@period, "-")),
criterion, (date>endWeek-IF(MOD(DAY(endWeek),7)=0,7,MOD(DAY(endWeek),7)))*(date<=endWeek),
selected,  FILTER(sales, criterion),
result,    SUM(selected),
result
)``````
Basically all that I tried to do was unravel the mess that is created by using text to describe the weekly periods. It is very much a 365 solution, so not much use in legacy (that is a polite way of saying 'obsolete') Excel at work.
Many Thanks I do appreciate the aid you have given, and Your aid has made a significant impact

Another formula that sums over weeks may be derived using WRAPCOLS
Code:
``````= LET(
monthSales, FILTER(sales, MONTH(date)=@month),
BYCOL(WRAPCOLS(monthSales, 7,""), SUM)
)``````
View attachment 87978
Many Thanks for your time and effort

I only do 365 formulae using defined names so my formula would start out as
Code:
``````= LET(
endWeek,   DATEVALUE(TEXTAFTER(@period, "-")),
criterion, (date>endWeek-IF(MOD(DAY(endWeek),7)=0,7,MOD(DAY(endWeek),7)))*(date<=endWeek),
selected,  FILTER(sales, criterion),
result,    SUM(selected),
result
)``````
Basically all that I tried to do was unravel the mess that is created by using text to describe the weekly periods. It is very much a 365 solution, so not much use in legacy (that is a polite way of saying 'obsolete') Excel at work.
please i am getting this massage and my Microsoft account is 365

Attachments

• Screenshot (1685).png
23 KB · Views: 10
• Screenshot (1684).png
11.7 KB · Views: 11
I think you simply decline the kind offer to modify the formula. The suggested relative references are not correct and, in any case, reverting to implicit intersection will not 'magic up' the missing functions for legacy systems.

You appear to be on the insider beta channel, in which case you may have access to PIVOTBY. Modern Excel formulas are less open to trial and error discovery than traditional methods but, given sufficient knowledge, the solutions can be far simpler.

Code:
``````= LET(
m, MONTH(date),
w, 1 + QUOTIENT(DAY(date) - 1, 7),
PIVOTBY(m, w, sales, SUM, , 0)
)``````

If the layout with interlaced headings is essential, that is possible, but as greater effort will go into the layout than went into the solution.

Attachments

• Aggregation by week of month.xlsx
62.5 KB · Views: 2
I think you simply decline the kind offer to modify the formula. The suggested relative references are not correct and, in any case, reverting to implicit intersection will not 'magic up' the missing functions for legacy systems.

You appear to be on the insider beta channel, in which case you may have access to PIVOTBY. Modern Excel formulas are less open to trial and error discovery than traditional methods but, given sufficient knowledge, the solutions can be far simpler.

Code:
``````= LET(
m, MONTH(date),
w, 1 + QUOTIENT(DAY(date) - 1, 7),
PIVOTBY(m, w, sales, SUM, , 0)
)``````

If the layout with interlaced headings is essential, that is possible, but as greater effort will go into the layout than went into the solution.
Thank you for your assistance. Your solution went above and beyond my expectations.