Excel Formula Help

Copper Contributor

I have a budget spreadsheet with multiple workbooks- one for each month that tracks each transaction and one that gives an overview of the different budget categories. I want to write a formula that will pull from the monthly tabs to give me a total on the overview sheet for each category. For example, I want it to look at the July sheet, recognize that column G says "Books" and then tally that for me on the overview sheet under the "Books" category. Can anyone help me do this?

1 Reply



Better yet, I'll suggest a way that Excel can do all "the heavy lifting" for you. No formula required.


If you're willing to do a slight reorganization of your workbook, you'll be amazed. First of all, it's a common mistake -- well intentioned but a mistake, nonetheless -- to break such things as expense tracking into monthly sheets.


Keep all your transactions in a single continuous table. Excel excels--by means of a tool called the Pivot Table--at taking single tables of transactions and creating cross-tabulated summaries, in this case, a month by month summary of expenses by category.


See the attached example. The Raw Data is there for several months in one sheet. The Pivot Table summarizes that raw data by month (and note that in the raw data the complete date is shown for each transaction) and by budget category, differentiating between expense and income. This is all done by the Pivot Table; no formulas needed to be written.


Read up on Pivot Tables here, and/or watch a YouTube video or two.