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