Pivot tables

[patches]

i have a pivot table of dates (mm/dd/yyyy) and $ amounts and need to sum up
the total amounts by month and year. How do i do this?

 

[Sean Timmons]

right-Click on a data, go to Group and Outline>Group. Select Months AND Years.

 

[Gary''s Student]

Say we have like:
date amount
02/11/10 44
03/05/10 43
01/08/09 60
10/24/09 97
01/21/10 17
04/27/09 69
04/02/10 91
01/02/09 60
06/20/09 11
02/05/10 66
02/26/09 97
09/25/09 95
05/04/09 75
04/05/09 56
01/14/09 2
03/15/10 88
04/15/09 84
05/27/09 95
01/27/09 6
06/01/09 86
01/11/09 26
02/10/09 49
07/27/09 75
01/20/10 82
04/10/10 67
01/22/09 90
03/30/09 94
04/27/09 47
01/24/09 9
06/25/09 17
08/04/09 30
03/03/10 66
01/14/10 39
10/04/09 12
08/01/09 73
01/18/09 81
02/10/09 71
01/13/09 68
09/23/09 29
10/30/09 19
10/21/09 31
06/11/09 71
02/11/09 39
03/30/09 66
08/08/09 19
11/21/09 55
01/21/10 99
12/29/09 68
06/06/09 54
10/27/09 80
in cols A & B. In C2 enter:

=YEAR(A2) & "-" & MONTH(A2)

we now see:

date amount new
02/11/10 44 2010-2
03/05/10 43 2010-3
01/08/09 60 2009-1
10/24/09 97 2009-10
01/21/10 17 2010-1
04/27/09 69 2009-4
04/02/10 91 2010-4
01/02/09 60 2009-1
06/20/09 11 2009-6
02/05/10 66 2010-2
02/26/09 97 2009-2
09/25/09 95 2009-9
05/04/09 75 2009-5
04/05/09 56 2009-4
01/14/09 2 2009-1
03/15/10 88 2010-3
04/15/09 84 2009-4
05/27/09 95 2009-5
01/27/09 6 2009-1
06/01/09 86 2009-6
01/11/09 26 2009-1
02/10/09 49 2009-2
07/27/09 75 2009-7
01/20/10 82 2010-1
04/10/10 67 2010-4
01/22/09 90 2009-1
03/30/09 94 2009-3
04/27/09 47 2009-4
01/24/09 9 2009-1
06/25/09 17 2009-6
08/04/09 30 2009-8
03/03/10 66 2010-3
01/14/10 39 2010-1
10/04/09 12 2009-10
08/01/09 73 2009-8
01/18/09 81 2009-1
02/10/09 71 2009-2
01/13/09 68 2009-1
09/23/09 29 2009-9
10/30/09 19 2009-10
10/21/09 31 2009-10
06/11/09 71 2009-6
02/11/09 39 2009-2
03/30/09 66 2009-3
08/08/09 19 2009-8
11/21/09 55 2009-11
01/21/10 99 2010-1
12/29/09 68 2009-12
06/06/09 54 2009-6
10/27/09 80 2009-10

Now we can do a standard pivot table of the sum of col B by col C:

Sum of amount
new Total
2009-1 402
2009-10 239
2009-11 55
2009-12 68
2009-2 256
2009-3 160
2009-4 256
2009-5 170
2009-6 239
2009-7 75
2009-8 122
2009-9 124
2010-1 237
2010-2 110
2010-3 197
2010-4 158
(blank)
Grand Total 2868