Using the SUM function to record weekly sales

How about posting:
1) some sample data
2) what you want to achieve
3) what you've tried that hasn't worked (eg your formula)

That way you'll get responses that are tailored to fit your situation.

Since you're copying the formula to the right....
try this:

Enter this formula wherever you want it:
=SUMPRODUCT((CEILING((COLUMN($B:$V)-COLUMNS($A:$A))/7,1)=COLUMNS($A:A))*$B$1:$V$2)

or...this shorter version with a "hardcoded" offset:
=SUMPRODUCT((CEILING((COLUMN($B:$V)-1)/7,1)=COLUMNS($A:A))*$B$1:$V$2)
Copy it across to the right.

ADDITIONAL COMMENTS:
The shortest method would be to use the OFFSET function, but that is a volatile function, meaning that it recalculates whenever the workbook calculates anything. Consequently, volatile functions can degrade calculation performance, resulting in sluggish workbooks.

If you happen to prefer the OFFSET approach....here's one utilization:

=SUM(OFFSET($A$1:$A$2,0,((COLUMNS($A:A)-1)*7)+1,2,7))

Is that something you can work with?

Sure thing.

Let's start with the original formula:
=SUMPRODUCT((CEILING((COLUMN($B:$V)-COLUMNS($A:$A))/7,1)=COLUMNS($A:A))*$B$1:$V$2)

Here's what it does...

PART I: Which columns do I want values from?
This section: COLUMN($B:$V)-COLUMNS($A:$A)
1) calculates the column numbers of columns B through V. {2,3,4...,22}
and
2) subtracts the number of columns before that range (one, in this instance)
returning {1,2,3,4...,21}

Then we divide each of those numbers by 7
BUT, we use the CEILING function to force all fractional values UP to the
next multiple of 1.
1/7= 0.142857142857143....becomes 1
2/7= 0.285714285714286....becomes 1
8/7= 1.14285714285714....becomes 2
etc

So, now we can assign each column to a specific 7-day period

Next, this part: COLUMNS($A:A)
calculates which 7-day period I want values from.  

If the formula is in Col_B...that section returns 1.
When it's copied to Col_D...that section returns 3.

When combined with the CEILING section, the verbal version of that section 
is: Check each column in the data range. Is it in the week I want?
A series of TRUE and FALSE values are returned. 
7 of them will be TRUE; all the others will be FALSE.

When TRUE and FALSE are used in arithmetic equations, Excel converts them 
to 1 and 0, respectively.

PART II: What's the total of the cells in the columns I want values from?
Each cell in the data area is multiplied by the 1 or 0 assigned to it in PART I. 
The end result is an array containing numbers from the cells in the week 
I care about and zeros for the cells in the other weeks.

The SUMPRODUCT function adds them all up. 

NOW....LETS CHANGE THE FORMULA TO SUIT THE NEW DATA RANGE

Starting with:
=SUMPRODUCT((CEILING((COLUMN($B:$V)-COLUMNS($A:$A))/7,1)=COLUMNS($A:A))*$B$1:$V$2)

1) We change the column identification references 
from this: COLUMN($B:$V) to this: COLUMN($F:$Z)

2) We change the negative adjustment references 
from this: COLUMNS($A:$A) to this: COLUMNS($A:$E)
(so we're subtracting 5 from the column number, insted of 1.)

3) We change the data range references 
from this: $B$1:$V$2 to this: $F$1:$Z$2

NOTE: we leave this section intact: =COLUMNS($A:A)
because it counts weeks for us.

The end result is this formula:
=SUMPRODUCT((CEILING((COLUMN($F:$Z)-COLUMNS($A:$E))/7,1)=COLUMNS($A:A))*$F$1:$Z$2)

I hope that helps.
Post back if you have more questions.