Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot tables
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot tables
right-Click on a data, go to Group and OutlineGroup. Select Months AND Years.
"patches" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot tables
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 -- Gary''s Student - gsnu200906 "patches" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Distributing Pivot Tables where pivot table data can't be accessed | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |