If you're interested, I took a shot at a dynamic array formula using MMULT instead of SUMPRODUCT, plus a few other array manipulation techniques. The attached file has only two formulas...
Cell H3:
=LET(
start, B7,
end, MAX(Model!D3:K1000),
CHOOSE(
XMATCH(B12, {"By Week","By Month","By Year"}),
SEQUENCE(, ROUNDUP((end - start + 1) / 7, 0), start, 7),
EDATE(start, SEQUENCE(, (YEAR(end) - YEAR(start)) * 12 + MONTH(end) - MONTH(start) + 1, 0)),
EDATE(start, SEQUENCE(, YEAR(end) - YEAR(start) + 1, 0, 12))
)
)
Cell H4:
=LET(
test, Model!D3:K1000 <> "",
contract, TOROW(IFS(test, Model!B3:B1000), 2),
quantity, TOROW(IFS(test, Model!C3:C1000), 2),
dates, TOCOL(IFS(test, Model!D3:K1000), 2),
start, +H3#,
end, CHOOSE(XMATCH(B12, {"By Week","By Month","By Year"}), start + 7, EDATE(start, 1), EDATE(start, 12)),
values, MMULT((contract = E4:E15) * (TOROW(dates) < F4:F15) * quantity, (dates >= start) * (dates < end)) * 2,
pad, EXPAND("",, COLUMNS(start), ""),
prev1, VSTACK(pad, DROP(values, -1)) = values,
prev2, VSTACK(pad, pad, DROP(values, -2)) = values,
rId, MOD(SEQUENCE(ROWS(values),, 0), 3) + 1,
IF(values = 0, "", CHOOSE(rId, values, IF(prev1, "", values), IF(prev1 + prev2, "", values)))
)
The date range and the results will update automatically when you change either the "Range Start" or the "View".
When I changed the view to "By Week" in your sample file, the results output to 18,324 cells in a fraction of a second.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks