Exceljet

Quick, clean, and to the point

Sum every 3 cells

Excel formula: Sum every 3 cells
Generic formula 
=SUM(OFFSET(first,0,(COLUMN()-COLUMN(current))*n, 1,n))
Explanation 

To write a formula that will sum "the next 3" cells each time it's copied, you can use the OFFSET function. In the example shown, the formula in O5 is:

=SUM(OFFSET($B5,0,(COLUMN()-COLUMN($O$5))*3, 1,3))

Note: the point of this formula is to eliminate the manual task of entering ranges manually with a single global formula, at the cost of a more complex formula.

How this formula works

At the core, the OFFSET function delivers a range of 3 cells to SUM, which returns a summed result.

The arguments for OFFSET are provided as follows:

For reference we use the first cell in the data range, B5, entered as a mixed reference (column locked, row relative).

For rows, we use 0, since we don't need to change rows.

For cols, we use the expression:

(COLUMN()-COLUMN($O$5))*3

This part of the formula figures out how many columns from the starting reference to offset. In O5, the offset is zero, in P5, the offset is 3, and so on.

Finally, height is input as 1 and width is input as 3, since in this case we always want a 1 x 3 range of cells.

Note: change 3 to the multiplier you need, shown as "n" in the generic form of the formula above.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.