Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles SUM 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:
Solution examples
Hi, I need a formula to work out how many values are less than a certain time value, simple "countif" do not work as it is a filtered spreadsheet. Here's the formula that I have got so far: SUMPRODUCT(SUBTOTAL(3,OFFSET(Current_Month!N$2:N$10000,ROW(Current_Month!N$2:N$10000)-MIN(ROW(Current_Month!N$2:N$10000)),,1)),--(Current_Month!N$2:N$10000>"24:0:0")
Solved by K. L. in 30 mins
I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula.
Solved by E. S. in 60 mins
I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula.
Solved by V. J. in 60 mins
Repost: the user to whom I connected earlier should pick up. others please don't pick it I need a formula written into cells (AI8 to EK8) to identify patterns and then count specific data. sheet name: SHOPEE PATTERNS ONE ROW have data of different shooter (wins and losses from cells B to H) that I need identify. There were 2 rows (AI4:EK4 and AI2:EK2) that will determine which cells to count and which to keep blank after applying the formula.
Solved by O. C. in 52 mins
i am using sum to work out balances imported from a csv. If there is no number in the blank column the sum does not work but if i enter 0 and then delete it it works. What is the work around on that? Not had this before
Solved by F. D. in 35 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc