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

[geo Jul]

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​

 

[Peter Bartholomew]

I only do 365 formulae using defined names so my formula would start out as

= 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.

 

[Peter Bartholomew]

Another formula that sums over weeks may be derived using WRAPCOLS

= LET(
    monthSales, FILTER(sales, MONTH(date)=@month),
    BYCOL(WRAPCOLS(monthSales, 7,""), SUM)
  )

 

[geo Jul]

I only do 365 formulae using defined names so my formula would start out as

= 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

 

[geo Jul]

Another formula that sums over weeks may be derived using WRAPCOLS

= LET(
    monthSales, FILTER(sales, MONTH(date)=@month),
    BYCOL(WRAPCOLS(monthSales, 7,""), SUM)
  )

View attachment 87978

Many Thanks for your time and effort

 

[geo Jul]

I only do 365 formulae using defined names so my formula would start out as

= 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

 

[Peter Bartholomew]

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.

= 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.

 

[geo Jul]

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.

= 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.