Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Perform case-sensitive Lookup in Excel
  • Vlookup Examples in Excel
  • Get nth match with INDEX / MATCH in Excel
  • How to use Excel ROW Function
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Analysis Examples

  • Excel Line Chart
  • How To Sort One Column or Multiple Columns in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • How to Sort by Color in Excel
  • Conditional Formatting Rules in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation allow uppercase only
  • Excel Data validation specific characters only
  • Excel Data validation no punctuation
  • Prevent invalid data entering in specific cells

Sum every n rows in Excel

by

This tutorial shows how to Sum every n rows in Excel using the example below;

Formula

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

Explanation

To sum every n rows, you can use a formula based on the OFFSET and SUM functions. In the example show, the formula in F4 is:

=SUM(OFFSET($C$3,(ROW()-4)*5,0,5,1))

How this formula works

In this example, there are 5 rows of data for each week (Mon-Fri) so we want to sum every 5 rows. To build a range that corresponds to the right 5 rows in each week, we use the OFFSET function. In F4 we have:

OFFSET($C$3,(ROW()-4)*5,0,5,1)

Cell C3 is the reference, entered as an absolute reference. The next argument is row, the crux of the problem. We need logic that will figure out the correct starting row for each week. For this, we use the ROW function. Because the formula sits in row 4, ROW() will return 4. We use this fact to create the logic we need, subtracting 4, and multiplying the result by 5:

(ROW()-4)*5

This will generate a row argument of 0 in F4, 5 in F5, and 10 in F6.

Worked Example:   How to use Excel OFFSET function

Column is input as zero, height as 5, and width as 1.

Worked Example:   Offset in Excel

The OFFSET function then returns a range to SUM (the range C3:C7 for F4), and SUM returns the sum of all amounts in that range.

Worked Example:   Average last 5 values in Excel

Post navigation

Previous Post:

Sum formulas only in Excel

Next Post:

Sum every nth column in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove last characters from right in a cell in Excel
  • How to check if cell contains some words but not others in Excel
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • Remove line breaks in a cell in Excel
  • Remove unwanted characters in Excel
  • How to calculate percent of year complete in Excel
  • Convert time to time zone in Excel
  • Get project midpoint in Excel
  • Next biweekly payday from date in Excel
  • Get last working day in month in Excel
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
  • How to calculate simple interest in Excel
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan interest in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles