Using the SUM function to record weekly sales

I would like to have a set of cells that add up all the sales within a given week. I know how to do this simply for one week, but how do I get Excel to automatically take this function and create the rest for future weeks? After entering the SUM function in one cell, I click and drag on the box to try to get Excel to correctly input the functions in the next cells (like how Excel will correctly input the next date, week, or month). But Excel doesn't do it correctly.

Thanks for the help!

Okay, for example:

Box 1: 9, 4, 3, 2, 9, 0, 8, 3, 0, 1, 4, 5, 7, 2, 4, 3, 7, 2, 5, 6, 5
Box 2: 3, 3, 3, 5, 6, 2, 9, 0, 6, 7, 4, 4, 2, 5, 2, 1, 0, 4, 6, 3, 4

So let's imagine that the data above refers to the number of boxes sold of each type each day. For reference, let's say the first piece of data after Box 1 (9) is cell B1

So I enter: =SUM(B1:H2) in order to add all the sales for the first seven days.

I now want the same function performed, but for the following week. I could manually enter the formula =Sum(I1:O2), but I am hoping there is a way to get Excel to automatically recognize that this is what I want to do, in the same way that you can enter a single date and then click the black surrounding box and drag to have the adjacent cells populated with the following dates. When I try this click and drag approach for totally the sales by week, the adjacent cell is populated with the follwing formula (using our example data): =SUM(C1:J2). This is adding up seven days of sales, but it does not begin where the previous formula left off. I have also tried manually entering the formulas in two adjacent cells (i.e. one cell has =SUM(B1:H2) and the one to its right has =SUM(I1:O2)). This does not work either.

Thanks for the help.

I tried out the very first formula you posted and that seems to work correctly. However, the cell letters and numbers I am really using (as opposed to those in the example) are different and I can't quite figure out how to adapt the formula specifically for my needs. The groups of cells that I need summed are as follows:

F3:L6
M4:S6
etc.

I'm hoping that if you tell me exactly how to stick those cell ranges into the first formula you provided I'll be able to adapt the formula whenever necessary.

Thanks a lot for your help!!!

Ron,

Thanks for the thorough explanation! It seems to be working fine now. A couple brief questions:

I changed the final formula so that the end reads:

$F$3:$Z$6

instead of

$F$1:$Z$2

because the data is between rows 3-6 rather than 1-2. I was correct to make that change, right? It wasn't working before with the modified formula you gave me and now it is working, so that obviously suggests that change was necessary. I suppose asking for confirmation from you on this is a bit unnecessary, but I just want to be 100% sure that I got it right.

My other question is regarding how many columns I have the formula check. Since I will eventually need to go beyond column Z, should I make the end of that formula read something like: $F$3:$ZZ$6 ? It seems to me that telling it to go all the way to column ZZ should cover me forever, since I doubt that my data will ever reach beyond that column.


Anyway, thanks again for taking the time to help me out. What a great program Excel is!

Actually, having the formula go all the way to ZZ makes it no longer work. Even going to AA messes it up. So how do I alter it to ensure that the formula will still encompass all my data once it goes beyond column Z?

Awesome! Everything is working fine!

I figured that inputting ZZ was not the most elegant way to get the results I wanted.

Okay, you are now free to help some other people. Thanks again!!!!