# Excel Formula group days into weeks

Discussion in 'Mac Apps and Mac App Store' started by ezekel, Jan 19, 2013.

1. ### ezekel macrumors regular

Joined:
Jun 4, 2004
Location:
Royal Oak
#1
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?

2. ### turtle777 macrumors 6502a

Joined:
Apr 30, 2004
#2
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