You can sum tables, columns, but what how do you add up every third row? In this article, we're going to show you how to sum every n rows using the SUM and the OFFSET functions. The OFFSET function's ability to return range is the key in this case.

Syntax

=SUM( OFFSET( absolute reference of the first cell of the values,

                ( ROW() - ROW(absolute reference of the first formula cell)) * n,

                0, n, 1) )

Steps

1. Start with the SUM function =SUM(
2. Continue with the OFFSET function OFFSET(
3. Select the first cell of the list to sum and make the reference absolute $C$3,
4. Open a parenthesis and use the ROW function (ROW()
5. Continue with a dash and add another ROW function with the absolute reference of the current cell –ROW($F$5)
6. Close the parenthesis and multiply the parenthesis value with n )*3,
7. Type in "0" for column argument 0,
8. Continue with 3 and 1 for height and width arguments and close the formula 3,1))

 How

To make any calculation on every Nth row, we need a dynamic range that returns different ranges with each formula. We can use the OFFSET function to do this. The OFFSET function can return reference for a single cell or a range from an anchor cell or range of cells.

The OFFEST function can take 5 arguments, the last 2 being optional, if you want to return a reference for a cell. Since we need a range that has n cells, we included them. Let's check the syntax and the arguments:

=OFFSET(reference, rows, cols, [height], [width])

Building up the formula

Next, we need to define the arguments. We can select the first cell of the values to sum ($C$3) as a base reference. This can be different cells as well, because we can control the where the return cell starts. However, to keep it simple, the first cell of values is a safe choice. The important point is to set the reference absolute.

The next step is to determine where the return range starts. This is the most important step because we select the start row of the return range. Since our sample modal is a vertical table, we used the ROW function which generates an incremental number, each time we copy formula to next row. To eliminate the surplus because of the start row, we use the ROW function again. However, the second ROW function is used with absolute reference that identifies the first formula cell ($F$5). The difference between two nullifies the surplus.

(ROW()-ROW($F$5))

Multiplying the incremental number from the ROW function with the n value gives the row number that identifies the start row of the return range. The number 3 is used because we need to get total of every 3 months.

(ROW()-ROW($F$5)) * 3

The column argument should be "0" because we choose the first cell of value column. As a result, we already are in the column we should be.

0

Finally, we enter the height and width arguments. The height should be equal to n, because every n rows criteria, and the width is 1 because our values are listed only in a single column.

3, 1

After the OFFSET function is set and ready, wrapping it with the SUM function concludes the formula.

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