Excel Formula group days into weeks

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

Most Liked Posts
  1. ezekel, Jan 19, 2013

    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?
     
    share
    + Quote Reply
  2. turtle777, Jan 19, 2013

    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
    + Quote Reply
(You must log in or sign up to post here.)

Share This Page