Jul 17 2021 03:28 AM
Jul 17 2021 03:44 AM
SolutionLet's say the formula =SUM(DataSheet!B2:B138) is in cell D2 of the summary sheet.
Change it to
=SUM(OFFSET(DataSheet!$B$2:$B$138, 137*(ROW()-2), 0))
or
=SUM(INDEX(DataSheet!B:B,137*(ROW()-2)+2):INDEX(DataSheet!B:B,137*(ROW()-2)+138))
Then fill down.
Jul 17 2021 04:17 AM
Jul 17 2021 04:22 AM
Jul 17 2021 04:26 AM
Great!
ROW() returns the row number of the cell with the formula. So for a cell in row 2, ROW() returns 2.
ROW()-2 returns 0 in row 2, 1 in row 3 etc.
137*(ROW()-2) returns 0 in row 2, 137 in row 3, etc.
OFFSET(DataSheet!B$2:B$138, 137*(ROW()-2), 0) is equivalent to B$2:B$138 in row 2, but to B$139:B$275 (that is, 137 rows down from B$2:B$137) in row 3, etc.
Jul 17 2021 11:38 AM
You didn't state what version of Excel you use so I held back because all my work is specific to Excel 365 and sometime beta channel only. It is macro-enabled because it contains VBA utility to upload Lambda functions which are of interest to me but not generally usable. The LET functions work with most Excel 365 installations.
The formula that is most likely to be usable is
= LET(
array, INDEX(data, SEQUENCE(10,Blocksize)),
vector, SEQUENCE(Blocksize,,,0),
MMULT(array, vector))
and least likely
= SUMROWSλ(
PIVOTλ(data, blocksize)
)
Jul 17 2021 03:44 AM
SolutionLet's say the formula =SUM(DataSheet!B2:B138) is in cell D2 of the summary sheet.
Change it to
=SUM(OFFSET(DataSheet!$B$2:$B$138, 137*(ROW()-2), 0))
or
=SUM(INDEX(DataSheet!B:B,137*(ROW()-2)+2):INDEX(DataSheet!B:B,137*(ROW()-2)+138))
Then fill down.