Table of Contents
To sum every nth row in Excel, use the SUM function along with the OFFSET function. The SUM function allows you to add up a range of cells, while the OFFSET function allows you to specify a starting cell and a number of rows to skip before selecting the next cell. By combining these two functions, you can sum every nth row in a spreadsheet. For example, if you want to sum every 3rd row in a column starting from cell A1, you would use the formula =SUM(OFFSET(A1,2,0,1,1)). This formula will give you the sum of cells A1, A4, A7, A10, and so on. You can change the starting cell and the number of rows to skip to suit your specific needs.
Sum Every Nth Row in Excel (With Examples)
You can use the following basic formula to sum every nth row in Excel:
=SUM(A1:A20*(MOD(ROW(A1:A20),4)=0))
This particular formula calculates the sum of every 4th row in the range A1:A20.
Simply change the value in the ROW() function to sum a different interval of rows.
For example, you can use the following formula to sum every 3rd row:
=SUM(A1:A20*(MOD(ROW(A1:A20),3)=0))
The following example shows how to use this formula in practice.
Example: Sum Every Nth Row in Excel
Suppose we have the following list of values in Excel:
We can use the following formula to sum every fourth row in the range:
=SUM(A1:A20*(MOD(ROW(A1:A20),4)=0))
The following screenshot shows how to use this formula in practice:
We can see that the sum of the values in every 4th row is 66.
We can verify this is correct by manually calculating the sum of every 4th row:
If you’d like to sum the value of every nth row starting with the first row, you can subtract 1 within the ROW() function of the formula:
=SUM(A1:A20*(MOD(ROW(A1:A20)-1,4)=0))
The following screenshot shows how to use this formula in practice:
We can verify that this formula calculates the sum of every 4th row starting with the first row:
Sum of Every 4th Row: 8 + 5 + 14 + 12 + 29 = 68.
This matches the value calculated by the formula.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I sum every nth row in Excel? Can you provide some examples?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-sum-every-nth-row-in-excel-can-you-provide-some-examples/
stats writer. "How can I sum every nth row in Excel? Can you provide some examples?." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-sum-every-nth-row-in-excel-can-you-provide-some-examples/.
stats writer. "How can I sum every nth row in Excel? Can you provide some examples?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-sum-every-nth-row-in-excel-can-you-provide-some-examples/.
stats writer (2024) 'How can I sum every nth row in Excel? Can you provide some examples?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-sum-every-nth-row-in-excel-can-you-provide-some-examples/.
[1] stats writer, "How can I sum every nth row in Excel? Can you provide some examples?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I sum every nth row in Excel? Can you provide some examples?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.