I'm making a log on one sheet called Daily. Each date has twenty rows for various data. On a second sheet, Stats, I want to calculate the data on Daily sheet for totals. So, for instance, I am trying to add all of the numbers in Column E between Rows 21 and 40 on Daily and put the total in a cell on Stats sheet with the same date. The trouble I'm having is when I try to Autofill 800 rows (just over two years in days), I get this:

=SUM(Daily!E21:E40)
=SUM(Daily!E22:E41)
=SUM(Daily!E23:E42)...

But I want this:

=SUM(Daily!E21:E40)
=SUM(Daily!E41:E60)
=SUM(Daily!E61:E80)...

I've tried filling in a few rows with the desired incremental formulas, but Excel doesn't see the pattern and just goes back to incrementing by 1 instead of the desired 20. I also tried Flash Fill - though I'm admittedly unfamiliar and it may not be used for that - but it also didn't see the pattern.

Is there a way to AutoFill formulas at set increments - in this case, 20 rows? If not, is there another way I can achieve this without hand-typing every formula 800 times?

I'm making a log on one sheet called Daily. Each date has twenty rows for various data. On a second sheet, Stats, I want to calculate the data on Daily sheet for totals. So, for instance, I am trying to add all of the numbers in Column E between Rows 21 and 40 on Daily and put the total in a cell on Stats sheet with the same date. The trouble I'm having is when I try to Autofill 800 rows (just over two years in days), I get this:

=SUM(Daily!E21:E40)
=SUM(Daily!E22:E41)
=SUM(Daily!E23:E42)...

But I want this:

=SUM(Daily!E21:E40)
=SUM(Daily!E41:E60)
=SUM(Daily!E61:E80)...

I've tried filling in a few rows with the desired incremental formulas, but Excel doesn't see the pattern and just goes back to incrementing by 1 instead of the desired 20. I also tried Flash Fill - though I'm admittedly unfamiliar and it may not be used for that - but it also didn't see the pattern.

Is there a way to AutoFill formulas at set increments - in this case, 20 rows? If not, is there another way I can achieve this without hand-typing every formula 800 times?

• excel
• excel-formula

3 Answers 3

Sure, you can use the OFFSET and ROW functions to "group" the required rows:

=SUM(OFFSET(Daily!$E$21, (ROW()-1)*20, 0, 20, 1))

note that the formula assumes that your summary starts in the first row, if not, you will have to adjust the offset value (ROW()-1)*20 (1 in the formula) to the row number where your summary starts from.

=MMULT(INDEX(E:E,SEQUENCE(,20,21)+{0;20;40}),SEQUENCE(20,,,0))

Or

=BYROW(INDEX(E:E,SEQUENCE(,20)+{2;4;6}*10),SUM)

Another alternative could be this formula. This formula can be entered in any cell and dragged down.

=SUM(INDIRECT(ADDRESS(21+(ROW(A1)-ROW($A$1))*20,5,,,"Daily!")):INDIRECT(ADDRESS(40+(ROW(A1)-ROW($A$1))*20,5,,,"Daily!")))