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))
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 columnB
).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 inarray1
.
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}
).
- Generates sequence numbers with a step value of 7, corresponding to the starting row of each 7-row block (e.g.,
lambda
:LAMBDA(x, SUM(FILTER(B2:B50, (ROW(B2:B50)>=x)*(ROW(B2:B50)<=x+6))))
- For each
x
inarray1
:FILTER(B2:B50, (ROW(B2:B50)>=x)*(ROW(B2:B50)<=x+6))
: Filters rows betweenx
andx+6
.SUM
: Totals the filtered rows.
- For each
In short, the MAP function applies the LAMBDA to each element in array1
, returning the total of every 7 rows.