Excel Formula group days into weeks

[ezekel]

This might become my giant excel question post. I'm working with a large spreadsheet and I'm trying to automate it as much as I can.

I have sales data as a spreadsheet. Data is by day. I want to summarize it into weeks.
A=day
B=sales

jan-1 $10
jan-2 $10
jan-3 $10
jan-4 $10
jan-5 $10
jan-6 $10
jan-7 $10

I want A to be grouped into the week and B into total sales for the week so it would look like

1/1 - 1/6 = $60
1/7 - 1/13 = $10

Is this possible?

 

[turtle777]

There are multiple solutions to this, but here's one of the top of my head.

Say your dates are in column A1 - Ax.
Create formula in column B: =WEEKNUM(A1). This calculates a calendar week number off of the dates.
Column C would contain the Sales data.

At the end of C, create a SUMIF formula: =SUMIF(B1:Bx,1,C1:Cx). This would add all Sales from Week 1.

Me personally, I would dump the data into a Table, and create a Pivot Table off of the data. Much cleaner and more flexible.

-t