Autofill SUM formula while skipping rows

**sooosan** · 10-21-2012, 03:40 PM

Hi, I've been searching the site and could not find anything specific to resolving my issues with autofill, so any help is greatly appreciated!

I have 2 sheets:
"Data" = Where all the daily numbers are input
"Weekly" = Where I'm trying to get a weekly total from pulling numbers from the "Data" tab

In the "Weekly" sheet, I have columns labled as follows:
Column A, Row 1 labeled: "Week"
Column A, Row 2 and forward labeled: "week1, week2, week3, etc."
Column B, Row 1 labaled: "Total"
Column B, Row 2 FORMULA: =SUM(Data!F3:F9) which is the total sales for the 7 days

How do I fill the formula to where column B, row 3 in the "Weekly" sheet takes the total of the next 7 days instead of me having to manually type in "=SUM(Data!F10:F16)"?

**FDibbins** · 10-21-2012, 03:43 PM

im sure that if you upload a sample of your workbook, i can help you

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**sooosan** · 10-21-2012, 03:54 PM

Here you go.

**FDibbins** · 10-21-2012, 04:24 PM

ok to simplify this, i added a helper column on sheet2 column G and entered this, to calc the week number...
=WEEKNUM(A2,2)

then on sheet1 i used this...
=SUMIF(Data!G:G,VALUE(MID(Weekly!A2,5,2))+38,Data!F:F)
the +38 is to adjust for the fact that your week1 is actually week 39

let me know how this works for you?

**sooosan** · 10-21-2012, 04:32 PM

I never thought of doing it that way, but it worked perfectly

Thank you so much for your help and promptness - really appreciate it!

**FDibbins** · 10-21-2012, 05:11 PM

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)