+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : categorize by month

  1. #1
    Registered User
    Join Date
    08-04-2009
    Location
    san diego,california
    MS-Off Ver
    Excel 2003
    Posts
    23

    Question categorize by month

    I have 2 coloumns one with the date and the other with numbers
    Eg
    Month No of jobs
    09/18/09 52
    09/23/09 34
    01/23/08 23
    02/11/08 87
    01/07/08 16
    07/07/07 11
    07/06/07 7
    05/06/07 32

    Now i want to know the no of jobs ran in the month of sep09,jan08,July07 separately...may be a table where i can select the month and sum of jobs ran in that month would be better
    Please let me know how i can acheive this...

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,815

    Re: categorize by month--please help

    Hi rds207, welcome to the forum.

    A pivot table would probably be your best bet in this instance. See attached for an example.

    Hope that helps!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-04-2009
    Location
    san diego,california
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: categorize by month--please help

    thank you so much paul,but how did you get the year column,its exactly the same how i need,am trying to get the sum of jobs ran per month...pls reply..
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-04-2009
    Location
    san diego,california
    MS-Off Ver
    Excel 2003
    Posts
    23

    Question Re: categorize by month--please help

    Quote Originally Posted by Paul View Post
    Hi rds207, welcome to the forum.

    A pivot table would probably be your best bet in this instance. See attached for an example.

    Hope that helps!
    thank you so much paul,but how did you get the year column,its exactly the same how i need,am trying to get the sum of jobs ran per month..see the attached file...pls reply..
    Attached Files Attached Files

  5. #5
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,815

    Re: categorize by month--please help

    Steps to re-create:

    1. Open workbook, select data range (e.g. A1:B464).
    2. Click Insert -> Pivot Table
    3. Click OK on the 'Create Pivot Table' dialog to create a PT on a new worksheet
    4. Drag the "RunDate" field to the Row Labels box (or Row labels area in Excel 2003 and earlier)
    5. Drag the "jobs_ran" field to the Values box (or Values area in Excel 2003 and earlier)
    6. Right-click on any date in the pivot table and choose "Group..."
    7. In the Grouping dialog, in the "By" section, select Months and Years so they are both highlited, then click OK
    8. Since the values area is still showing a count, you can change it to sum by right-clicking on any of the count data (or the cell labeled "Count of jobs_ran" near the top) and choose Summarize Data By -> SUM. (Alternatively, you can right-click one of those cells and choose Value Field Setings, select SUM and click OK.)

    That should do it!

  6. #6
    Registered User
    Join Date
    08-04-2009
    Location
    san diego,california
    MS-Off Ver
    Excel 2003
    Posts
    23

    Thumbs up Re: categorize by month

    thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1