Data Sort - Excel

[ALawson]

I have two columns of information, the first if financial applications and the second is the date. I would like to group and sum the monthly totals, what is the quickest way to do this. I can easily sort the by ascending date but am stumped on the monthly summation.

Any ideas?

Cheers

Alex

[Noger]

It might not be the quickest, but I would create a third column to extract the month, and then use Data/Subtotal to do the summation based on that.

[viscous circles]

Add a column and enter the month for each row and then put all this info in to a pivot table. You can then total for each month.

[ALawson]

I am a pivot table numpty and don't quite follow, what should I do have the months and year in a seperate column, which parts of the pivot table do I need to place the data in?

Here is a sample amount of data.

£1,592,048.00 13-Dec-01
£966,821.00 16-Jan-02
£989,374.00 04-Feb-02
£1,508,234.00 07-Mar-02
£465.00 2-Apr-02
£5,695.04 04-Apr-02
£1,219,838.00 12-Apr-02
£108,728.30 12-Apr-02
£87,233.30 18-Apr-02
£43,820.99 01-May-02
£257.45 01-May-02
£9,414.16 01-May-02
£218.37 01-May-02
£210.85 01-May-02

Edited by ALawson on Thursday 20th September 13:21

[Noger]

Unless I am missing something, you don't need a pivot table.

Just add a third column with =MONTH( "date_cell" ) in it and then sort it, then do a Data/Subtotal on "For each change of Month", and "Add Subtotal to Amount" (asuming you have Amount and Month as column headings). It will subtotal by month.

Edited by Noger on Thursday 20th September 13:32

[viscous circles]

As ever in Excel, there are various ways to do 1 task.

You can use a pivot table or use the subtotal function. Using a Pivot table you end up with:

Sum of Money
Month/Year Total
Apr 2002 £1,421,959.64
Dec 2001 £1,592,048.00
Feb 2002 £989,374.00
Jan 2002 £966,821.00
Mar 2002 £1,508,234.00
May 2002 £53,921.82
Grand Total £6,532,358.46

And using the Subtotal function you get:

Month/Year Money
Dec 2001 £1,592,048.00
Dec 2001 Total £1,592,048.00
Jan 2002 £966,821.00
Jan 2002 Total £966,821.00
Feb 2002 £989,374.00
Feb 2002 Total £989,374.00
Mar 2002 £1,508,234.00
Mar 2002 Total £1,508,234.00
Apr 2002 £465.00
Apr 2002 £5,695.04
Apr 2002 £1,219,838.00
Apr 2002 £108,728.30
Apr 2002 £87,233.30
Apr 2002 Total £1,421,959.64
May 2002 £43,820.99
May 2002 £257.45
May 2002 £9,414.16
May 2002 £218.37
May 2002 £210.85
May 2002 Total £53,921.82
Grand Total £6,532,358.46

[ALawson]

Sorted

Many Thanks.