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

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

See Attached.

Steve

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

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

DeNae

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

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

8. ## 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>

9. ## 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

10. ## 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. ## 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.

#### Posting Permissions

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