Dynamic Formula to Sum Every 7 Rows in Excel

Published on

To sum every 7 rows, you can use either a drag-down formula or a dynamic array formula in Excel. This method is especially useful when you want to calculate weekly totals in a column that contains data entered in chronological order.

How to Use a Drag-Down Formula to Sum Every 7 Rows in Excel

If you prefer a drag-down formula, which is undoubtedly the simplest option, try this:

=SUM(OFFSET(range_start, (ROW(A1)-1)*7, 0, 7, 1))

Replace range_start with the starting cell reference in absolute reference format (e.g., $B$2). No other changes are required.

Example:

Assume you have dates in column A (A2:A) and corresponding sales data in column B (B2:B). To calculate weekly totals, use the following formula in cell C2 and drag it down:

=SUM(OFFSET($B$2, (ROW(A1)-1)*7, 0, 7, 1))
Example of summing every 7 rows in Excel using a drag-down formula

When applying this formula to a different dataset, replace $B$2 with the first cell reference of your range. You can leave A1 unchanged.

How the Formula Works:

The OFFSET function dynamically adjusts the range it sums based on the row number. The syntax is as follows:

=OFFSET(reference, rows, cols, [height], [width])
  • reference: $B$2 (starting cell of the range).
  • rows: (ROW(A1)-1)*7 (adjusts the starting point to sum every 7 rows: 0 rows, then 7 rows, then 14 rows, and so on).
  • cols: 0 (no column offset, stays in column B).
  • height: 7 (sums 7 rows at a time).
  • width: 1 (sums a single column).

Create a Dynamic Array Formula to Automatically Sum Every 7 Rows

If you want to sum every 7 rows in one go, use the MAP function, one of Excel’s powerful LAMBDA-based functions.

Example:

If the data is in B2:B50, enter this formula in cell C2:

=MAP(
   SEQUENCE(ROUNDUP(ROWS(B2:B50)/7, 0), 1, 2, 7), 
   LAMBDA(x, SUM(FILTER(B2:B50, (ROW(B2:B50)>=x)*(ROW(B2:B50)<=x+6))))
)

Using this formula to sum every 7 rows is simple. You just need to replace all occurrences of B2:B50 with your actual data range and adjust the number 2 (row number where the range starts) as needed. However, understanding the formula might seem complex because it involves nested functions like MAP and LAMBDA. Let’s break it down.

Formula Breakdown:

MAP Syntax:

=MAP(array1, lambda)
  • array1: The input array on which the LAMBDA function operates.
  • lambda: A custom function applied to each element in array1.

In Our Formula:

  • array1: SEQUENCE(ROUNDUP(ROWS(B2:B50)/7, 0), 1, 2, 7)
    • Generates sequence numbers with a step value of 7, corresponding to the starting row of each 7-row block (e.g., {2; 9; 16; 23; 30; 37; 44}).
  • lambda: LAMBDA(x, SUM(FILTER(B2:B50, (ROW(B2:B50)>=x)*(ROW(B2:B50)<=x+6))))
    • For each x in array1:
      • FILTER(B2:B50, (ROW(B2:B50)>=x)*(ROW(B2:B50)<=x+6)): Filters rows between x and x+6.
      • SUM: Totals the filtered rows.

In short, the MAP function applies the LAMBDA to each element in array1, returning the total of every 7 rows.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

How to Extract Numbers from Text in Excel with Regex

You can use the REGEXEXTRACT or REGEXREPLACE functions to easily extract numbers from text...

Using OFFSET and MATCH Together in Google Sheets: Advanced Tips

One powerful and flexible way to look up values is by combining the OFFSET...

How to Use OFFSET and XMATCH Functions Together in Excel

We often use the OFFSET and XMATCH functions together to match a value in...

How to Calculate Maximum Drawdown in Excel and Google Sheets

You can use the following dynamic array formula to calculate maximum drawdown (MDD) in...

More like this

How to Extract Numbers from Text in Excel with Regex

You can use the REGEXEXTRACT or REGEXREPLACE functions to easily extract numbers from text...

How to Use OFFSET and XMATCH Functions Together in Excel

We often use the OFFSET and XMATCH functions together to match a value in...

Excel Tutorial: Calculate Nights Between Dates in Each Month

When working with date ranges in Excel, you might need to calculate how many...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.