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
     

Share This Page