Need to sum up daily data into weekly automatically

[solo9999]

Hi all,

Happy & Healthy New Year!

I have the following scenario:I have an Excel sheet where data is entered daily in the following format:

.......A............B
01/01/2013 | 1,750
01/02/2013 | 2,350
01/03/2013 | 8,150
...etc...

What I want to do is a weekly data summary, that will scan through the dates and sum up only the values which date belongs to the respective week:

WK1 (01/01 to 01-06): sum all B values for that date range
WK2 (01/07 to 01/13): sum all B value for that date range

How can this be done?

Thanks!

 

[Gideon1973]

What if you create a formula over the date to return the week number? Then create a pivot table to group by the week number and sum the values?

That's the quick and dirty using WEEKNUM(A2) to return 1, 2, ..., but if you want it to have "fancier" formatting (such as "WK1 (1/1 to 1/6)") then you'd have a bit more work to get the expression to "look" the way you want.

But perhaps that's what you're looking for?

 

[SydneyGeek]

You can use a pivot table too. Create the pivot, add your dates to the Row Labels area, right-click one of the dates in the pivot and Group.
Pick Days only, and adjust the spinner on the dialog to 7.

Now build the rest of the pivot table to summarise your sales / values.

Denis