How to Sum Every Nth Row in Google Sheets (With Examples)


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:

sum every nth row in Google Sheets

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:

The sum of every sixth row turns out to be 19.

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.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Multiply Column by a Constant
Google Sheets: How to Multiply Column by a Percentage
Google Sheets: How to Rank Items by Multiple Columns

Leave a Reply

Your email address will not be published.