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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hello, I have an excel data where I update one row (with multiple columns) each week. During plotting, I go back every n weeks (n rows) to generate average plot of each of these columns. It is difficult to manually choose n rows everytime I update a new row. Is there a way to automate this so that once I update a new row, a plot is automatically generated going back n rows from this new row? Thanks!
Solved by S. F. in 14 mins
I have daily records with some amount, I need a formula to give me sum of every 7 rows
Solved by M. Y. in 12 mins
Hide #N/A rows
Solved by I. Q. in 11 mins

Leave a Comment

avatar