Snasui Excel Forum

Posted: **Thu Nov 13, 2025 11:34 pm**

Hi all
I need your help to solve the query as in attach
I need dynamic array excel 365

Thanks

Posted: **Fri Nov 14, 2025 9:19 am**

Try this formula from Copilot,

=LET(
  dates, SORT(A5:A29),
  sales, SORTBY(B5:B29, A5:A29),
  n, 6,
  groupCount, ROUNDUP(ROWS(dates)/n, 0),
  idx, SEQUENCE(groupCount),
  startIdx, (idx - 1) * n + 1,
  endIdx, IF(startIdx + n - 1 > ROWS(dates), ROWS(dates), startIdx + n - 1),
  dateLabels, BYROW(idx, LAMBDA(i, TEXT(INDEX(dates, INDEX(startIdx, i)), "dd-mmm-yy") & " to " & TEXT(INDEX(dates, INDEX(endIdx, i)), "dd-mmm-yy"))),
  weekNos, idx,
  groupSums, BYROW(idx, LAMBDA(i,
    LET(
      s, INDEX(startIdx, i),
      e, INDEX(endIdx, i),
      range, SEQUENCE(ROWS(sales)),
      mask, (range >= s) * (range <= e),
      SUMPRODUCT(sales, mask)
    )
  )),
  HSTACK(dateLabels, weekNos, groupSums)
)

Explanation:

dates – Sorts the order dates from the range A5:A29
sales – Sorts the sales values to match the sorted dates
n – Number of days per group (e.g., 6 days per group)
groupCount – Calculates the total number of groups, rounded up
idx – Sequence of group numbers (e.g., 1, 2, 3, …)
startIdx – Starting row index for each group
endIdx – Ending row index for each group (capped at the last row)
dateLabels – Creates a label like "02-Nov-24 to 07-Nov-24" for each group
weekNos – Week number (e.g., 1, 2, 3, …)
groupSums – Calculates total sales for each group using SUMPRODUCT with a logical mask
HSTACK(...) – Combines dateLabels, weekNos, and groupSums into the final output table

Posted: **Fri Nov 14, 2025 11:04 pm**

Thanks for such awesome

Posted: **Sat Nov 15, 2025 10:30 pm**

I try it but no luck
=LET(
Data, B4:C28,
C,CHOOSECOLS,
D, C(Data,1),
S, C(Data,2),
Start, MIN(D),
WkNum, ROUNDUP((D - Start + 1) / 7, 0),
Wk, UNIQUE(WkNum),

WkStart, Start + (Wk - 1) * 7,
WkEnd, MIN(MAX(D), WkStart + 6),

Results, HSTACK(
TEXT(WkStart, "dd-mmm-yy") & " to " & TEXT(WkEnd, "dd-mmm-yy"),
Wk,
SUMIFS(S, WkNum, Wk)
),
VSTACK({"Dates", "Week No", "Total Sales"}, Results)
)

Posted: **Sat Nov 15, 2025 11:19 pm**

If you want to display the header, try this,

Code: Select all

=LET(
  dates, SORT(A5:A29),
  sales, SORTBY(B5:B29, A5:A29),
  n, 6,
  groupCount, ROUNDUP(ROWS(dates)/n, 0),
  idx, SEQUENCE(groupCount),
  startIdx, (idx - 1) * n + 1,
  endIdx, IF(startIdx + n - 1 > ROWS(dates), ROWS(dates), startIdx + n - 1),
  dateLabels, BYROW(idx, LAMBDA(i, TEXT(INDEX(dates, INDEX(startIdx, i)), "dd-mmm-yy") & " to " & TEXT(INDEX(dates, INDEX(endIdx, i)), "dd-mmm-yy"))),
  weekNos, idx,
  groupSums, BYROW(idx, LAMBDA(i,
    LET(
      s, INDEX(startIdx, i),
      e, INDEX(endIdx, i),
      range, SEQUENCE(ROWS(sales)),
      mask, (range >= s) * (range <= e),
      SUMPRODUCT(sales, mask)
    )
  )),
  VSTACK({"Dates","Week No","Total Sales"},HSTACK(dateLabels, weekNos, groupSums))
)

Posted: **Sun Nov 16, 2025 3:14 pm**

Thanks you

Snasui Excel Forum

Excel query 1

Excel query 1

Re: Excel query 1

Re: Excel query 1

Re: Excel query 1

Re: Excel query 1

Re: Excel query 1