Sums of every n rows in a column in Excel

Sums of every n rows in a column in Excel

Suppose there is a column of data points. We want a new column of data points which consists of the sums of every n data points in the old column continuously. For example, if there are 1000 data points in the old column, and we want every 5 data points to be summed up to a new data point, there will be 200 data points in the new column.

The formula to use is =SUM(OFFSET(r,(ROW()-s)*n+s-ROW(),0,n,1)) or =SUM(OFFSET(r,(n-1)*(ROW()-s),0,n,1)), where r is the reference and s is the row of the first data point. See the screenshot below.

Fig. 1.  Sums of every 4 rows in a column.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

More information about formatting options

To prevent automated spam submissions leave this field empty.