To sum every n cells to the right (columns) we can use the SUM function with OFFSET and COLUMN in Google Sheets. What about calculating the sum of n cells (rows) to the bottom?

I that case, the combination uses the ROW function instead of COLUMN.

This post contains two key formulas;

1. Formula to sum n columns/cells to the right.
2. Formula to sum n rows/cells to the bottom

How to Sum Every N Cells (Columns) to the Right in Google Sheets

Sample Data: The range to sum is B3:3 (third row from column B).

When I drag the formula in B6 to the right, it sums every 3 cells/columns to the right. I’ll explain.

In cell B6 the sum range is B3:D3. What happens when the formula copied to cell C6? The range to sum changes to E3:G3.

Similarly, the sum range in cell D6 will be H3:J3 and cell E6 will be K3:M3.

Actually the range is not changing, I am offsetting 3 cells (columns) to the right within SUM.

Assume I have used the below SUM formula in cell B6 and then dragged (copied) to cell C6.

=sum(B3:D3)

It will change to =sum(C3:E3), not =sum(E3:G3).

So one thing is clear! To sum every n cells/columns to the right, we can’t use the SUM function ‘alone’ in Google Sheets.

Here is the workaround using the above-said SUM, OFFSET, and COLUMN combo.

The Formula in Cell B6 to Sum Every 3 Cells to the Right by Dragging:

=sum(offset($B$3,0,(column()-column($B$3))*3,1,3))

Drag to the right until it returns a 0.

Usage Note 1

If you insert this formula in any column other than B, you must change the cell reference within the formula column($B$3) with the corresponding column letter.

For example, the total sum range is B3:M3 or you can say B3:3 (infinite row reference). If you insert my above formula in cell H6, the formula must be like this.

=sum(offset($B$3,0,(column()-column($H$3))*3,1,3))

See the change in the ‘column’ part of the formula.

How the Formula Skips Every 3 Cells When Dragging to the Right?

To know that first of all go through the OFFSET function syntax. That’s important here.

Syntax:
OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

There are 5 arguments including the optional two arguments in OFFSET. We must make use of all of the 5 arguments.

We have the following input values;

cell_reference – $B$3 (which is the starting cell reference in the range to sum)
offset_rows – 0 (We don’t want to offset any rows).
height – 1 (we have only one row in our range to sum)
width – 3 (the element that decides ‘n’) – to sum every six cells, you must specify the ‘width’ as 6.

What we don’t have is the input for offset_columns.

What we are going to do with the OFFSET is, offset ‘n’ (read 3) columns from B3. Then get ‘n’ (read 3) columns of values (width).

Here we want to dynamically offset_columns.

The first time, it should be 0, then when dragging it should be 3, then 6, and so on.

Here is that dynamic offset_column. It plays a very important role to sum every n (read 3) cells/columns to the right. How?

(column()-column($B$3))*3

In the inserted cell (in column B6) it will return 0 as column() will return 3 (current column numer) and column($B$3) will also return 3 (0-0*3=0).

When you are dragging the formula to the right, the formula column() will return 4. But column($B$3) will remain the same as absolute cell reference used. So it would be (4-3)*3=3. That means;

=sum(offset($B$3,0,3,1,3))

Again when you dragging, the offset_column will be (5-3)*3=6.

=sum(offset($B$3,0,6,1,3))

Yes! The dynamic offset_column returns 0 then the multiplications of 3 like 3, 6, 12, 15…

Remember the width is also 3. So the formula in B6 skips 0 columns and returns values from 3 columns (width).

In C6 it skips 3 columns and returns values from the next 3 columns and so on.

That’s all about how to sum every n cells/columns to the right in Google Sheets.

How to Sum Every N Cells (Rows) to the Bottom in Google Sheets

To sum every n cells to the bottom, we must concentrate on the offset_rows and height arguments.

Here, the height is 3 (the ‘n’ element). Instead of dynamic offset_column, we need dynamic offset_rows this time.

The input values that we already have:

cell_referece – $D$2 (the starting cell reference from the range to sum)
offset_columns – 0 (no columns to offset)
height – 3 (the element that decides ‘n’)
width – 1 (we have only one column in the range to sum, which is column D)

What we are missing this time is dynamic offset_rows. We can use our dynamic offset_columns formula here by modifying the COLUMN function to ROW.

Here is that formula.

(row()-row($D$2))*3

Usage Note 2

My above formula is in the row#2 (H2).

If I use the above dynamic offset_rows formula in any other row, for example in M5, the formula must be modified to (row()-row($D$5))*3.

The Formula in Cell H2 to Sum Every 3 Cells to the Bottom by Dragging:

=sum(offset($D$2,(row()-row($D$2))*3,0,3,1))

This way we can sum 3 cells (rows) to the bottom. That’s all. Enjoy!