DEV Community

loading...

Excel Formulas to Sum the Values from every N Rows!!

excelgeek profile image Andrews Originally published at geekexcel.com on ・3 min read

Here, we will show how to sum or subtotal the values from every N rows in Excel Office 365 using the simple formula. Further, we will explain the basic syntax with a practical example. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Formulas to sum every N rows
Formulas to sum every N rows

General Formula:

  • To sum the values from every n rows in Excel, use the below formula.

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

Syntax Explanations:

  • SUM – The Excel SUM Function will help to add the values in the cells.
  • OFFSET – In Excel, the OFFSET function returns a reference to a range constructed in parts which is a starting point, a row, and column offset, and a final height and width in rows and columns.
  • ROW – It will return the row number for reference. Read more on the ROW Function.
  • Minus Operator (-) – This symbol will help to subtract any two values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.
  • Range – It represents the input values given in the worksheet.
  • Comma symbol (,) – It is a separator which helps to separate a list of values.
  • Criteria -It is the condition that helps to sum the values.

Example:

  • In the below image, we are going to see how to sum the values in every N rows.
  • First, we will give the input values in Column C.
  • Then, enter the given formula in the selected area.
  • Finally, it will show the result in the selected Cell F2.

Example
Example

Bottom-Line:

Here, we guided you to learn the simple formulas used to sum the values from every N rows in Excel Office 365. Hope you like this article. Kindly, share your worthwhile feedback in the below comment section. Thank you so much for Reading!! Keep learning on Geek Excel!! *and Excel Formulas *!!

Read Ahead:

Discussion

pic
Editor guide