Excel formula: Sum every n rows

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:

How do I compute the sum or an average of a column with 5000+ rows when some of the cells have #N/A N/A values? I would like any function (average for instance) to ignore those lines. Can you please help?

An Excelchat Expert solved this problem in 19 mins!

I am trying to use the following formula to sum a column of six items if any item is nonzero but show nothing if they are all zero. If the first, second or sixth item is nonzero, it works. But if the those three are zero, the sum ignores the third, fourth, and fifth items. Here is the formula: =IF(N(F56)*N($J$2)+N(F57)*N($J$3)+N(F58)*N($J$4)+N(F59)*($J$5)+N(F60)*N($J$6)+N(F61)*N($J$7)=0,"",SUM(F56:F61))

An Excelchat Expert solved this problem in 12 mins!

I have define 3 ranges per cell as: 1 = Satisfactory 0= Unaceptable N/A= No Applicable I want to SUM the cells and when the SUM of all cells is N/A I want to set up as 100%, if the sum of all cells is 1 and if the sum of al cells is "0" provide me 0%. Permits/Authorizations Used 100% 0101 Part A Work details N/A 0102 Part B Work scope & description N/A 0103 Part C Specialty safety equipment 1 0104 Part D Safety precautions N/A

An Excelchat Expert solved this problem in 25 mins!