Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Months/year in sumproduct (Excel 2000)

    I am building a schedule that will have an account number in rows and a Beginning Balance, then a column for each month of the year, then a calculated ending balance. The source will be a data table of items, one of which is a "Date placed in Service" in the form 1/3/2005 or 2/15/2005 or 1/31/1998 and so on. So I thought I would do a sumproduct to go through the table for each account and sum the cost placed in service for that month. I am hoping to use the column's 1/31/05, 2/28/05 etc. in the sumproduct formula. My question: Is there an easier way to specify within the sumproduct formula than constantly saying "Greater than 1/1 and less than 2/1" and so forth? Can I get a column heading 1/31/05 in some way inside the sumproduct function to sum the "January-05" dates placed in service? Or will I have to go the greater than / less than route. I think it would be easier to understand. Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Months/year in sumproduct (Excel 2000)

    Why not create a pivot table to summarize the results?

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Months/year in sumproduct (Excel 2000)

    If the date field is mm/dd/yyyy, how could I fool it into just aggregating based on mm/yy? Wouldn't I get a column for every unique mm/dd/yyyy date? And I don't want to have to split out day month and year data into special cells. This stuff has to be done fast, so I want to take what I've been given, dump it into this ss and get my results. My first idea is always to pivot what I have, but one also gets into formatting quirks and so on that limit the degree to which I can take a table and tailor its presentation for higher levels. I might just have to play around with formatting a little more, but PTs are tough to get flexible as to colmuns and so on. Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Months/year in sumproduct (Excel 2000)

    When you have created the pivot table, click on the date field, then select Data | Group and Outline | Group... and specify that you want to group by Months.

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Months/year in sumproduct (Excel 2000)

    OK. Let me try that. But this table has 12,600 rows, and that might bomb-out the table size-wise, so I couldn't get to the grouping menu. My first preference is always a pivot, so let me work on the suggestion. If only we had more formatting flexibility apart from those hokey autoformat setups. And especially since we lose the formats each time we redo the table-which I can sortof understand-it's too bad one cannot create one's own autoformat library. Thanks.

Posting Permissions

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