Sequencing

Pegasus3245

New member
Joined
Nov 17, 2012
Messages
1
Reaction score
0
Points
0
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.

Thanks in advance.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 
Top