Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

ezekel

macrumors regular
Original poster
Jun 4, 2004
112
0
Royal Oak
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?
 
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
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.