Sum of Single Value over Multiple Tables over Multiple Sheets

Copper Contributor

I'm currently working on tracking my expenses in an Excel document and would like a page dedicated to my annual spend. I titled this page "Overview" and here, I would like to show the total amount of money that went to specific "Items" being restaurants, entertainment, personal, etc. I would first like to break it down into specific items such as "Subway, Wing Stop, Pizza Hut" etc. and then categorize those into broader fields such as "Food, Automotive, etc."

The problem I am having is getting sums for the "Items" listed in the "Monthly Items" on the Overview page. I am attempting to gather all the total cost values through each month (each sheet Jan- Dec) for a specific item (such as Wing Stop) and display that value on the Overview page under the "Percentage" column. I would actually prefer it wasn't a percentage but a numerical value. I just need to change the title for that column. 

I have attempted SUM, SUMIF, SUMIFS, and none have seemed to work for me. I even downloaded third party tools such as Kutools to possibly find an easier way around it, but have yet to find a solution. 
Is this calculation even possible? If so, could someone please help me with a solution to this? 

Thank you

2 Replies

@VANTEROUS My first advise would be to get rid of the monthly schedules. Just enter all your income and expenses in one well structured table, and create your summaries from there. The table could have columns like Date, Type (Income/Expense) Category (Food, Automotive, etc.), Sub-category (Subway, Wingstop, Pizzahut, etc.) and Amount.

 

Pivot tables can help you summarise all these transactions by date, month, quarter or year without much trouble.

Download you monthly bank statements and combine them all in one excel table, then create a pivot table from the information on the bank statement, and make the necessary naming adjustments.