Forum Discussion
Solved
I need to subtotal similar items in a checkbook spreadsheet monthly by category
I have a spreadsheet for a checkbook used by a condo association. We display subtotals every month of different expenditure types such as lawn care, maintenance, insurance, legal, utilities, etc. The spreadsheet has fields for date and category. I need to build a new spreadsheet within the workbook to display these subtotals for each category(the number of categories doesn't change) for each month. What is the easiest way to do this?
You write: I need to build a new spreadsheet within the workbook to display these subtotals for each category(the number of categories doesn't change) for each month.
And I first want to challenge that "need," by suggesting that what you really need is to display the subtotals for each category for each month. That is to say, whether or not a "new spreadsheet within the workbook" is what you need is questionable. Are you aware of the Pivot Table? Since your database already identifies months (dates) and categories, the Pivot Table would readily grant you your subtotals, with the ability to identify all of the backup details as needed. And a Pivot Table would work without new formulas, could be refreshed at will.....
Here's a good resource to learn about PivotTables.
If you want more guidance, might I suggest that you post a copy of your current workbook, either on OneDrive or GoogleDrive, posting here a link that grants access to it.
You write: I need to build a new spreadsheet within the workbook to display these subtotals for each category(the number of categories doesn't change) for each month.
And I first want to challenge that "need," by suggesting that what you really need is to display the subtotals for each category for each month. That is to say, whether or not a "new spreadsheet within the workbook" is what you need is questionable. Are you aware of the Pivot Table? Since your database already identifies months (dates) and categories, the Pivot Table would readily grant you your subtotals, with the ability to identify all of the backup details as needed. And a Pivot Table would work without new formulas, could be refreshed at will.....
Here's a good resource to learn about PivotTables.
If you want more guidance, might I suggest that you post a copy of your current workbook, either on OneDrive or GoogleDrive, posting here a link that grants access to it.
Thanks for your idea of the pivot table. I'm having difficulty in some of the formatting around a pivot table. The file that I have attached is just something created quickly so that I could learn about the pivot tables. I have typed in the word September since I can't figure out how to get the "Sep" formatted for the complete month name. If this can't be done, can I how can I get rid of the "Sep" label? Also, same question with the words "Sum of Amount". How do I not display these words?
I have come to the conclusion that the pivot table makes a nice intermediate tool for summing the amounts by month. The reports that I need cannot rely solely on the pivot table however, as some possible fields may not be used in a given month. Example: We only pay insurance once per quarter. The report needs to display amounts spent on insurance in a given month whether we did or not. Since the pivot table may not display the insurance field in the early months, I can't rely on it to display in the reports. I can however use the data in the pivot table in the reports.
Unless someone can help with the formatting, it appears that the pivot table isn't always a good tool for reports which need to formatted in a certain way. Again, I think it is a good intermediate step.
