Here are a couple to get you started.
In the summary sheet create a column of 'week commencing' dates
In cell E8 put the date for the first Monday in the Year, say 04-Jan-2010
In the cell below (E9) put =E8+7 and this will give you the start of the next week.
Drag the formula in E9 down 52 rows and you have the dates for each Monday in the year.
In F8 enter this formula
(it assumes dates are in column C on sheet 2 and the data is in column AJ)
I split the formula on to two lines for ease of viewing.
This formula multiplies three things together - note the * signs.
The first thing it does is to look at each cell in the range Sheet2!$C$3:$C$367 and compares it to the value in cell E8
If the date in any cell in column AJ is equal to or greater than the date in E8 it returns a value of 1, but if not in this date range it returns a value of zero.
As you know anything x 0 is zero.
So all cells that are less than the date in E8 will be zero
The next part does the same sort of calculation but this time it returns 1 only if the cell has a date less than or equal to 6 days after the start of the week in question.
Now only cells with a date in that week return 1 * 1 which equals 1
All other cells return zero.
Finally it takes the value in the cells in column AJ.
It sums the values in all the cells in all the rows 3 to 367, after multiplying them by what it has so far which is 1 or zero.
Thus only cells in column AJ that are in rows with dates in that week will return a value and be added together, and you have a weekly total.
Note the $ signs in some parts of the formula.
When you drag the formula down the 50 or so rows, the parts of the addresses with $ signs in front will not change, so the ranges on sheet 2 stay fixed but the formula picks up new dates from column E.
You can do the same for Monthly data.
Put 01-January-2010 in cell H8
In cell H9 put this formula:
You will need to have the analysis toolpack AddIn loaded for this to work -
Click the Office button
Select Excel options at the bottom
Select the AddIn tab on the left
Click Go at the bottom next to Manage ExcelAddIns (the default option in the drop-down box)
Check the box against Analysis ToolPak
Drag the formula down 11 rows and you have the 12 months as Excel dates, ( it won't work if you type in "January" etc.)
EOMONTH takes the date, in this case 01 Jan 2010 in H8 and calculates the last day of the month, 0 months later, i.e., with 0 as the second parameter you get the last day of the month - it handles leap years as well.
Then +1 goes to the next day which is the first of the next month and you now have a list of months - format the cells with "mmmm" to just show the month.
In cell I8 enter this formula:
Its just the same as before with a start date taken from H8 and an end date calculated using EOMONTH again.
Drag the formula down 11 rows and you have the 12 month's totals
Put this formula in cell J8:
This calculates the number of days in the month and divides the total sales by the number of days
DateDif with "d" returns the number of days between two dates.
There are lots of ways to get to these weekly, monthly, annual results.
You may get other solutions - just pick one that you like and keep using it, so that you get really used to it.