Forum Discussion

Tbear_beardsley's avatar
Tbear_beardsley
Copper Contributor
Jul 17, 2021
Solved

Range changing in increments

I need some help. I’m summarising data from one sheet into another, the amount of data points is in the thousands. In my summary sheet I’m summarising around 250 data rows into one row on a seperate s...
BI & Data Analysis
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jul 17, 2021

    Tbear_beardsley 

    Let'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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 17, 2021

Tbear_beardsley 

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)
   )

Resources