Excel query 1

[sna]

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

Thanks

[snasui]

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:

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