Table of Contents
Google Sheets has some useful functions to help you sum every nth row. You can use the OFFSET, SUM, and ROW functions to calculate a running total for every nth row. The OFFSET function will allow you to set the row number for the start of your sum, the SUM function will do the addition, and the ROW function will provide the row number for the individual rows in the sum. You can use these functions in combination to quickly sum every nth row in Google Sheets.
You can use the following syntax to sum the values in every nth row in Google Sheets:
=sumif(ArrayFormula(mod((row(A1:A)-row(A1)+1),3)),0,A1:A)
This particular formula sums every 3rd value in column A, starting at cell A1.
The following examples show how to use this syntax in practice with the following column of values in Google Sheets:
Example 1: Sum Every Third Row
We can use the following formula to sum the values in every third row (starting in cell A1) of column A:
=sumif(ArrayFormula(mod((row(A1:A)-row(A1)+1),3)),0,A1:A)
The following screenshot shows how to use this syntax in practice:
The sum of every third row turns out to be 37.
We can manually verify this by taking the summation of every third row:
Sum: 15 + 10 + 3 + 9 = 37.
Example 2: Sum Every Sixth Row
We can use the following formula to sum the values in every sixth row (starting in cell A1) of column A:
=sumif(ArrayFormula(mod((row(A1:A)-row(A1)+1),6)),0,A1:A)
The following screenshot shows how to use this syntax in practice:
We can manually verify this by taking the summation of every sixth row:
Sum: 10 + 9 = 19.
Example 3: Sum Every Third Row Starting at Row 2
We can use the following formula to sum the values in every third row (starting in cell A2) of column A:
=sumif(ArrayFormula(mod((row(A2:A)-row(A2)+1),3)),0,A2:A)
The following screenshot shows how to use this syntax in practice:
The sum of every third row (starting in cell A2) turns out to be 16.
We can manually verify this by taking the summation of every third row, starting at cell A2:
Sum: 8 + 6 + 2 = 16.