Excel Formula group days into weeks

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

  1. ezekel macrumors regular

    Jun 4, 2004
    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.

    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

    Apr 30, 2004
    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.


Share This Page