< Go Back

Sum every n rows

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.

Formula

The generic formula to sum every n row is:

=SUM(OFFSET(ref_cell,(ROW()-offset)*n,0,n,1))

Where,

  • 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.

Explanation

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.

Example

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.

=SUM(OFFSET($B$2,(ROW()-7)*5,0,5,1)))

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar