I’m working with two sheets where Sheet1 has my raw data and Sheet2 shows summary calculations. I need to create a formula that sums every 5 cells in a row, then when I copy it to the next column, it should automatically move to the next group of 5 cells.
| A |
B |
C |
D |
| 1 |
=SUM(Sheet1!A3:E3) |
=SUM(Sheet1!F3:J3) |
=SUM(Sheet1!K3:O3) |
When I manually type the first few formulas and try to drag them across, Google Sheets doesn’t recognize the pattern I want. Instead of jumping by 5 columns each time, it just moves one column at a time. How can I make the formula automatically skip to the correct range when I drag it horizontally? I need this to work so I don’t have to manually type each formula.
Try SUMPRODUCT with MOD - it’s way simpler than complex formulas. Use =SUMPRODUCT((MOD(COLUMN(Sheet1!A3:Z3)-1,5)=0)*(Sheet1!A3:Z3)) and adjust your range. But honestly? Sometimes a helper column with the ranges written out beats fighting with fancy formulas.
OFFSET is way cleaner for this pattern. Use: =SUM(OFFSET(Sheet1!A3,0,(COLUMN(A1)-1)*5,1,5)). It takes your starting point (A3) and shifts horizontally by column number times 5. The last two numbers grab 1 row and 5 columns of data. When you drag across, COLUMN(A1) becomes COLUMN(B1), COLUMN(C1), etc., so it auto-calculates the right offset. I like this better than INDIRECT - it’s faster and doesn’t hit column limits. Just use A1, B1, C1 in the COLUMN function, not the actual cell reference, or it won’t increment when you copy.
I’ve run into this exact problem before. Here’s what worked for me - use INDIRECT with COLUMN: =SUM(INDIRECT(“Sheet1!”&CHAR(65+(COLUMN()-1)*5)&“3:”&CHAR(65+(COLUMN()-1)*5+4)&“3”)). The formula figures out which column range to grab based on where you are. CHAR converts numbers to letters (A=65, B=66, etc.), and (COLUMN()-1)*5 finds the starting column for each 5-column group. Drag it across and it’ll automatically jump to the right intervals. Just heads up - this only works for the first 26 columns. If you’ve got a bigger dataset, you’ll need something more complex with OFFSET instead of INDIRECT and CHAR.