Exceljet

Quick, clean, and to the point

Sum every n rows

Excel formula: Sum every n rows
Generic formula 
=SUM(OFFSET(A1,(ROW()-offset)*n,0,n,1))
Summary 

To sum every n rows, you can use a formula based on the OFFSET function and the SUM function. In the example show, the formula in F4 is:

=SUM(OFFSET($C$5,(ROW()-4)*5,0,5,1))

where n=5 because each week contains 5 rows of data.

Explanation 

In this example, the goal is to calculate a weekly total using the data as shown. Notice each week corresponds to 5 rows of data (Monday-Friday) so we will need to sum a different range every 5 rows. To build a range that corresponds to the correct 5 rows for each week, we use the OFFSET function. To sum the values returned by OFFSET, we use the SUM function. The complete formula in cell F4, copied down, is:

=SUM(OFFSET($C$5,(ROW()-4)*5,0,5,1))

Calculating ranges

The OFFSET function returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. In the example shown, we use OFFSET inside of SUM like this:

OFFSET($C$5,(ROW()-4)*5,0,5,1)

The starting reference is C5, since this is the first value in the data. Notice we use an absolute reference because we don't want to this reference to change later when we copy the formula down. The next argument, rows, the crux of the problem because this is where we need logic that will figure out the correct starting row for each week.

To calculate the right starting point, we use the ROW function. ROW returns the row number for a given reference or, if no reference is provided, the row number of the cell containing the formula. Because the formula sits cell F4, ROW() will return 4. We use this fact to create the logic we need. We first subtract 4 as an "offset" because we we want to create a zero-based index, for reasons made clear below. Then we multiply the result by 5, this is how this snippet will evaluate in cells F4, F5, and F6:

F4=(ROW()-4)*5 // returns 0
F5=(ROW()-4)*5 // returns 5
F6=(ROW()-4)*5 // returns 10

To finish off the arguments for OFFSET, we provide cols as zero, because we want to stay in the same column. We provide height as 5 because we want a range that contains 5 rows. Finally, we provide width as 1, because we want a range that contains 1 column. After the row logic runs, this is how OFFSET works F4, F5, and F6:

F4=OFFSET($C$5,0,0,5,1) // returns C5:C9
F5=OFFSET($C$5,5,0,5,1) // returns C10:C14
F6=OFFSET($C$5,10,0,5,1) // returns C11:C19

Summing ranges

The final step in the problem is just to sum the ranges provided by OFFSET. This is done with the SUM function. After OFFSET is evaluated, the resulting range is delivered directly the SUM function, which sums the values in the range and returns a final result. As the formula is copied down into F4:F6, we get the final totals per week:

SUM(C5:C9) // returns 220
SUM(C10:C14) // returns 202
SUM(C11:C19) // returns 285

To recap: OFFSET returns calculates the correct range for each week using the row logic explained above. The SUM function sums the ranges returned by OFFSET.

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas