Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot tables
I have a spreadsheet that I use SUMPRODUCT or SUMIFS to summarise data into a
consolidation sheet. The data that I need to summarise has the following columns (amongst others) that need to be taken into account: Date (if the date falls into the month of September, then the data is summed in the September column) Classification (there are four classification types) Amount (the amount that is summed) Everything works brilliantly except my staff cannot use the spreadsheet because they are too resource heavy. I heard about Pivot Tables the other day and tried one out. It is summarising the data how I would like it, but it uses every day as a criteria and I cant work out how to summarise that data for say September in a September column instead of summarising it for every day it finds in September Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot tables
In your data, create an additional column called "Month". Have it use the
formula: =DATE(YEAR(A2),MONTH(A2),1) To group all your dates into similar months. Then have your PivotTable use this field. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MurrayBarn" wrote: I have a spreadsheet that I use SUMPRODUCT or SUMIFS to summarise data into a consolidation sheet. The data that I need to summarise has the following columns (amongst others) that need to be taken into account: Date (if the date falls into the month of September, then the data is summed in the September column) Classification (there are four classification types) Amount (the amount that is summed) Everything works brilliantly except my staff cannot use the spreadsheet because they are too resource heavy. I heard about Pivot Tables the other day and tried one out. It is summarising the data how I would like it, but it uses every day as a criteria and I cant work out how to summarise that data for say September in a September column instead of summarising it for every day it finds in September Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot tables
Clever! Thank you!
"Luke M" wrote: In your data, create an additional column called "Month". Have it use the formula: =DATE(YEAR(A2),MONTH(A2),1) To group all your dates into similar months. Then have your PivotTable use this field. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MurrayBarn" wrote: I have a spreadsheet that I use SUMPRODUCT or SUMIFS to summarise data into a consolidation sheet. The data that I need to summarise has the following columns (amongst others) that need to be taken into account: Date (if the date falls into the month of September, then the data is summed in the September column) Classification (there are four classification types) Amount (the amount that is summed) Everything works brilliantly except my staff cannot use the spreadsheet because they are too resource heavy. I heard about Pivot Tables the other day and tried one out. It is summarising the data how I would like it, but it uses every day as a criteria and I cant work out how to summarise that data for say September in a September column instead of summarising it for every day it finds in September Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot tables
On Oct 9, 5:08*pm, MurrayBarn
wrote: Clever! Thank you! "Luke M" wrote: In your data, create an additional column called "Month". Have it use the formula: =DATE(YEAR(A2),MONTH(A2),1) To group all your dates into similar months. Then have your PivotTable use this field. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "MurrayBarn" wrote: I have a spreadsheet that I use SUMPRODUCT or SUMIFS to summarise data into a consolidation sheet. The data that I need to summarise has the following columns (amongst others) that need to be taken into account: Date (if the date falls into the month of September, then the data is summed in the September column) Classification (there are four classification types) Amount (the amount that is summed) Everything works brilliantly except my staff cannot use the spreadsheet because they are too resource heavy. I heard about Pivot Tables the other day and tried one out. It is summarising the data how I would like it, but it uses every day as a criteria and I cant work out how to summarise that data for say September in a September column instead of summarising it for every day it finds in September Any ideas? Thank you. it worked great for me too. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update linked pivot tables when page changes in main pivot | 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) |