Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum each month in multi-year data (2000/SP3)

    I have a spreadsheet with 35K rows of data. I've attached an example sheet. I need to calculate the totals for Total, Free, and Used for each calendar month. Obviously, I'd like to put together one formula that could just be copied down the rows! I can figure how to calculate what month and year the date is, and I even got most of the way through constructing an IF to compare it to a specific cell and then sum the whole mess. But I can't figure out how create a formula that doesn't require referring to a list of potential month/year combinations.

    I don't have to have a full formula, but some guidance on how to put it together would be very welcome!

  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: Sum each month in multi-year data (2000/SP3)

    How about a pivot table?

    See Attached.

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum each month in multi-year data (2000/SP3)

    Unfortunately, a PivotTable has a limit of 32,500 items per field. We currently have 35,405 items in each column.

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

    Re: Sum each month in multi-year data (2000/SP3)

    How about subtotals - see attached workbook. I added a calculated column Month; formula =Date(Year(A2),Month(A2),1) and filled down, then sorted on this column. Added subtotals on the three size fields breaking on Month.

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum each month in multi-year data (2000/SP3)

    That gave me what I needed, Hans! Thank you again!

    DeNae

  6. #6
    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: Sum each month in multi-year data (2000/SP3)

    That is the limit of unique items, not the limit of the total items. (Do you have that many unique items in the list?)

    In XL97 there is a limit of 8,000 unique items, but I can create a pivot table with 65,536 rows of data...

    Steve

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Minneapolis, Minnesota, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum each month in multi-year data (2000/SP3)

    No, we have that many unique items in the list, unfortunately.

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum each month in multi-year data (2000/SP3)

    Hi,
    Here is a nice trick to get rid of the word "Total" that the Subtotals feature adds automatically: (In your case, the word Total does not look too good in the string of dates in the month column!)

    1. Select the entire month column.
    2. Choose Edit | Replace.
    3. In the Find box type "Total"
    4. Leave the Replace box blank.
    5. Ensure that Search is by Column and Find entire cells only is de-activated!
    6. Click Replace All.

    this leaves just the date in the Month column and reads a little clearer.

    (Totally optional) - Thought i'd just leave it as a thought! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  9. #9
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,160
    Thanks
    45
    Thanked 13 Times in 11 Posts

    Re: Sum each month in multi-year data (2000/SP3)

    This may be stupid Hans, but on the left hand side of your example, you have the pages listed with open and close (+ -) How did you do that?
    Thanks
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  10. #10
    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: Sum each month in multi-year data (2000/SP3)

    Excel does it automatically when you add subtotals (Data - Subtotals) or groups (data - group and outline)

    Steve

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sum each month in multi-year data (2000/SP3)

    Once you have these expand/colapse markers available via outlining or subtotals, you can also use the little known shortcut key combination of CTRL + 8 to hide or show the markers. I find this VERY useful after I have used consolidate with links. The consolidate feature puts the expand/colapse markers on the left sothat you can view the linked values from the source files. I'm not keen on them as it complicated the worksheet if you expand them. So I simply press ctrl + 8 to hide the markers.
    Regards,
    Rudi

Posting Permissions

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