Forum Discussion

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

HansVogelaar

MVP

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

=SUM(INDEX(DataSheet!B:B,137*(ROW()-2)+2):INDEX(DataSheet!B:B,137*(ROW()-2)+138))

Then fill down.

Tbear_beardsley
Copper Contributor
Jul 17, 2021
Perfect. Can you explain the row situation. And would it be similar if I wanted to do the same along the columns for data going across?
- Tbear_beardsley
  Copper Contributor
  Jul 17, 2021
  Never mind found out I just have to take the $ off the B but leave it on the number
  - HansVogelaar
    MVP
    Jul 17, 2021
    Tbear_beardsley
    
    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.