SOLVED

I need to subtotal similar items in a checkbook spreadsheet monthly by category

Copper Contributor

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?

7 Replies
best response confirmed by James_Stallcup (Copper Contributor)
Solution

@James_Stallcup 

 

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.

@James_Stallcup 

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?

I put that quote from your first post here, so I (and other potential advice-givers) don't lose sight of the fact that this is a condo association check book that you're maintaining, not your own; that, therefore, there are expectations in terms of reporting that might not apply in the same way if it were your own personal expenses.

 

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.

 

Let me invite you to hold off on final conclusions until you've worked with the concept a bit more. For one thing, you should extend the set of raw data to include a lot more months of expenses, to see how it looks and works. There are some aspects of the Pivot Table that you probably haven't discovered yet. 

 

I've attached an example of a similar spreadsheet with raw data summarized using the Pivot Table. It still is pretty simple, but I want to draw your attention to a couple of features.

  • This spreadsheet records both income and expenses. There's a column in the RawData table (which could be hidden if desired) called AdjAmt, for "adjusted amount." The "adjustment" referred to is displaying all expenses as a negative amount; income as positive. That way the Pivot Table's grand totals show the net of Income minus Expenses. That may or may not be of use to you. (I incorporate that idea in my own personal budget tracking spreadsheet, which incorporates data from multiple check accounts and multiple credit cards.) 
  • In a Pivot Table, if you click on any one of the summary totals, you'll be presented with a sheet that shows all of the back up data. In this sample sheet (as in yours) there's not much to show here, but in my own, where (for example) there are months when we spend a lot on restaurants, it's helpful to see all of the individual charges with a single click. 
  • You did discover the "Filter" capability, but in your case I don't think you need it, since it makes more sense (I would submit) to see September expenses next to August and October, (etc). Filter might come in handy if you want to look, say, at income or expenses for different buildings within the Condo (the Coop that my wife and I live in has five high-rise buildings; whether or not they look at things that way, I don't know!)

 

Now, with regard to your insurance example--paid quarterly but reported monthly--I'm sure there are ways to handle that. One way (which might not be acceptable) is to do all your reporting on a quarterly basis--which Excel can do for you based on the dates. Another would be to have an expense type called "budgeted expense" wherein you record that budget amount on a monthly basis, and then still record the actual quarterly expense, but mark it in such a way that it doesn't appear in the Pivot Table.

[I do this on my personal budget tracking in this way: I pay our credit card bills in total each month, but the check that goes out to pay is marked "Ignore" and the payment as recorded by the Credit Card company is similarly marked "Ignore"--because what I'm interested in tracking is the individual transactions that are recorded as expenses in each credit card statement. The cashflow out from checking and in to the credit card is "neutral" so long as I'm accounting for all of the transactions that make up that credit card bill. Yet I've retained the data should I need to go back and reconstruct it.]

 

I'm also attaching a slightly revised version of your spreadsheet, with changes to the display of the Pivot Table.

@mathetes Check out my revised workbook. I now have columns in my data for payee, sub-category, and category. This is more of what I want to display on the report. I have added two payees that don't appear with payments at the top of the data so that they will be displayed with $0  values in the report. If I do this for all 33 of my possible payees they will all appear in the report as a sub-cat and cat item. 

Question: If I want to add a column to the right of the Dec column in the report showing Monthly budget for a sub- category item, how would i do that? Same question for yearly budget to the right of the grand total? 

           Thanks for your help so far.

@James_Stallcup 

 

I like the direction you're going in with the sub-categories. Maybe it's just a quirk of mine, but I personally would always put a sub-category to the right of the category (it somehow seems more logical there). For what it's worth, in my own budget tracking spreadsheet, I have created a way to have ALL of those entries data-validated, with the sub-category validation list based on the category already chosen. Given a bit more time, I'll send you a further  revision that incorporates that.

 

A quick response. I'll give it some more thought. There is a capability that I've not used (not sure I can) called Power Pivot, which might be able to join data from two tables to produce exactly what your're asking about.

 

A quicker (& dirtier) solution to your question about displaying monthly and annual budgets is the one I've put into this, which just adds two VLOOKUP columns off to the right of the "normal" Pivot Table.

@James_Stallcup 

 

Here's a revised version that enables you to add (dynamically) new sub-categories and categories, automatically updating the data-validation for the column in your checkbook where sub-cats are entered, and automatically looking up and entering the category based on that prior entry.

 

Let me know if the quick and dirty way I incorporated the budget figures is altogether inadequate. I'll see what I can do, but I'm not optimistic. 

1 best response

Accepted Solutions
best response confirmed by James_Stallcup (Copper Contributor)
Solution

@James_Stallcup 

 

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.

View solution in original post