1. ## Sequencing

Hi All,
Really struggling with a simple seqence. I have 4 years of sales data, I want to display these as a weekly total for each week. All my daily sales are in 1 colum.

I want to have:-
b1 =sum(a1:a7)
b2 = sum(a8:a14)
b3 = sum(a15:a21)
and continuing....

when i use the sequence dragger thingie mabob down it simply incriments each row so instead of getting b4 = sum(a22:a28), im getting b4 =sum(a16:a22).

Im pretty sure this is a simple vba string then drag it down, but i cannot find the solution anywhere; all i can find is complex codes for dragging down text and number strings being incrimented by a specific character or a specific number etc.

Any help would be greatly apreciated.  Reply With Quote

2. Yes, this isn't really a drag thing, as you're incrementing by more than one row at a time. You pretty much have to reach for a more complicated function set to dynamically build the SUM range.

One version, using INDIRECT: =SUM(INDIRECT("A"&7*ROW(A1)-6&":A"&7*ROW(A1)))

Another version using OFFSET: =SUM(OFFSET(A\$1:A\$7,7*ROW(A1)-7,0))

Both of these would be entered in B1 then copied down.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•