| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Report by two categories.
Folks,
I have the following data: Date Product Subtotal Tax 01/12/2004 BA 253.36 25.36 01/16/2004 BA 456.36 12.36 02/05/2004 BA 546.36 12.36 02/18/2004 BA 1236.54 25.69 02/19/2004 BZ 650.36 36.36 02/25/2004 BZ 456.69 10.36 03/12/2004 BZ 789.36 21.36 I need to create a report that will give me a subtotal by Product and Date. Now, the problem with doing a straight subtotal (and then a nested subtotal) is that I will have too many subtotals by Date (imagine my dataset spanning over several years with multiple monthly dates). Is there a way, I can flag a certain number of date occurence as a particular month. For example, from my data above, can I summarize BA in January 2004 as $747.44. So, although, there are two occurences in January, my report is neatly summarized as January 2004. Thanks. Regards, Shams. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Report by two categories.
Shams --
You can do this pretty easily with the PivotTable tool. It allows you to set up a table "logically" and then it just fills it in with summarized data. "Shams" wrote: Folks, I have the following data: Date Product Subtotal Tax 01/12/2004 BA 253.36 25.36 01/16/2004 BA 456.36 12.36 02/05/2004 BA 546.36 12.36 02/18/2004 BA 1236.54 25.69 02/19/2004 BZ 650.36 36.36 02/25/2004 BZ 456.69 10.36 03/12/2004 BZ 789.36 21.36 I need to create a report that will give me a subtotal by Product and Date. Now, the problem with doing a straight subtotal (and then a nested subtotal) is that I will have too many subtotals by Date (imagine my dataset spanning over several years with multiple monthly dates). Is there a way, I can flag a certain number of date occurence as a particular month. For example, from my data above, can I summarize BA in January 2004 as $747.44. So, although, there are two occurences in January, my report is neatly summarized as January 2004. Thanks. Regards, Shams. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Report by two categories.
Thanks for the info. I tried doing a Pivot selecting the Product and Date
column as Rows and then summing by subtotal. It is still giving me the same dept of information as the Subtotal functiuon would have. My goal is still quite simple. I want to show that under Product BZ, the total for February was $1,153.77. I don't want to specify the two dates under February but roll them up under February. Let me know if I am missing something. Thanks. "pdberger" wrote: Shams -- You can do this pretty easily with the PivotTable tool. It allows you to set up a table "logically" and then it just fills it in with summarized data. "Shams" wrote: Folks, I have the following data: Date Product Subtotal Tax 01/12/2004 BA 253.36 25.36 01/16/2004 BA 456.36 12.36 02/05/2004 BA 546.36 12.36 02/18/2004 BA 1236.54 25.69 02/19/2004 BZ 650.36 36.36 02/25/2004 BZ 456.69 10.36 03/12/2004 BZ 789.36 21.36 I need to create a report that will give me a subtotal by Product and Date. Now, the problem with doing a straight subtotal (and then a nested subtotal) is that I will have too many subtotals by Date (imagine my dataset spanning over several years with multiple monthly dates). Is there a way, I can flag a certain number of date occurence as a particular month. For example, from my data above, can I summarize BA in January 2004 as $747.44. So, although, there are two occurences in January, my report is neatly summarized as January 2004. Thanks. Regards, Shams. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Report by two categories.
You need to group the dates by month (right click in the dates column in the
pivot table and select group, then select month) -- Regards, Peo Sjoblom "Shams" wrote in message ... Thanks for the info. I tried doing a Pivot selecting the Product and Date column as Rows and then summing by subtotal. It is still giving me the same dept of information as the Subtotal functiuon would have. My goal is still quite simple. I want to show that under Product BZ, the total for February was $1,153.77. I don't want to specify the two dates under February but roll them up under February. Let me know if I am missing something. Thanks. "pdberger" wrote: Shams -- You can do this pretty easily with the PivotTable tool. It allows you to set up a table "logically" and then it just fills it in with summarized data. "Shams" wrote: Folks, I have the following data: Date Product Subtotal Tax 01/12/2004 BA 253.36 25.36 01/16/2004 BA 456.36 12.36 02/05/2004 BA 546.36 12.36 02/18/2004 BA 1236.54 25.69 02/19/2004 BZ 650.36 36.36 02/25/2004 BZ 456.69 10.36 03/12/2004 BZ 789.36 21.36 I need to create a report that will give me a subtotal by Product and Date. Now, the problem with doing a straight subtotal (and then a nested subtotal) is that I will have too many subtotals by Date (imagine my dataset spanning over several years with multiple monthly dates). Is there a way, I can flag a certain number of date occurence as a particular month. For example, from my data above, can I summarize BA in January 2004 as $747.44. So, although, there are two occurences in January, my report is neatly summarized as January 2004. Thanks. Regards, Shams. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Report by two categories.
Peo,
Thank you very much for your help. It now works very well... "Peo Sjoblom" wrote: You need to group the dates by month (right click in the dates column in the pivot table and select group, then select month) -- Regards, Peo Sjoblom "Shams" wrote in message ... Thanks for the info. I tried doing a Pivot selecting the Product and Date column as Rows and then summing by subtotal. It is still giving me the same dept of information as the Subtotal functiuon would have. My goal is still quite simple. I want to show that under Product BZ, the total for February was $1,153.77. I don't want to specify the two dates under February but roll them up under February. Let me know if I am missing something. Thanks. "pdberger" wrote: Shams -- You can do this pretty easily with the PivotTable tool. It allows you to set up a table "logically" and then it just fills it in with summarized data. "Shams" wrote: Folks, I have the following data: Date Product Subtotal Tax 01/12/2004 BA 253.36 25.36 01/16/2004 BA 456.36 12.36 02/05/2004 BA 546.36 12.36 02/18/2004 BA 1236.54 25.69 02/19/2004 BZ 650.36 36.36 02/25/2004 BZ 456.69 10.36 03/12/2004 BZ 789.36 21.36 I need to create a report that will give me a subtotal by Product and Date. Now, the problem with doing a straight subtotal (and then a nested subtotal) is that I will have too many subtotals by Date (imagine my dataset spanning over several years with multiple monthly dates). Is there a way, I can flag a certain number of date occurence as a particular month. For example, from my data above, can I summarize BA in January 2004 as $747.44. So, although, there are two occurences in January, my report is neatly summarized as January 2004. Thanks. Regards, Shams. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple charts in ChartSpace; problems with double Categories | Charts and Charting in Excel | |||
| Multiple charts in ChartSpace; problems with double Categories | Charts and Charting in Excel | |||
| How do I create a push report in Excel? | Excel Discussion (Misc queries) | |||
| Linking worksheets after runnning report | Excel Discussion (Misc queries) | |||
| Microsoft Access Report into Excel Spreadsheet | Excel Discussion (Misc queries) | |||
Linear Mode
