When we don’t want to sum all the rows in a worksheet, and instead, we might only be interested in specific rows. To get the sum of every n rows in Excel, we can use a combination of OFFSET and SUM functions.
The generic formula to sum every n row is:
- Offset is the constant number, representing the cell number where we are inserting the formula. If we are inserting the formula in cell C2, then offset will be 2.
- N is every n rows that we want to sum. If you want to sum every 5 rows, then n will be 5.
We do not have any specific formula in Excel to sum every n rows. Instead, we use the formula based on Excel OFFSET and SUM functions to accomplish this. To have a clear understanding of how this formula works, consider the example below.
In this example, we have a data sheet that shows entries of revenue made in various months of the year. As you can see, each month has 5 entries. This means that our n is 5. To get the sum of each month revenue, we need to sum every 5 rows using above formula syntax.
Figure 1. The example of how to sum every n row
Suppose we want to enter this formula in cell D7, then insert following formula in cell D7 and copy it down to other rows below.
It is also necessary for you to note that cell B2 is our reference cell and has been entered as an absolute cell. The ROW is actually the crux of our problem, and without paying proper attention to it, you might fail to get the correct answer. Row function figures out the correct starting row for each month. Also notice that our formula shall be in row 7, thus ROW() function will return 7. To create a proper logic, we shall use this fact and we shall subtract 7 and multiply it with 5, which is our n rows value. The formula will yield the summation of every 5 rows as shown below:
Figure 2. The result of how to sum every n row
In our case, the Excel OFFSET function has returned the range of values to sum. The SUM function, on the other hand, has returned the summation of the ranges specified.