Excel - Formulas that total a value in multiple sheets?

Question

I have an excel with 12 sheets named each month of a year to keep my expenses. I want to have another sheet to have a total of a specific expenses such rent, petrol,shopping etc.

How to this so I can select an expense and have the total of all the months?

E.g. how much i spend from January till September for the petrol.

Answer 1

Follow the golden rule: Data on one sheet, reporting on others.

By creating a sheet for each month, you are effectively entering data into report sheets, and it will be really hard to create formulas that report across these twelve sheets.

Instead, re-arrange your workbook and move/enter all data on one sheet. Use a date column to identify which date an entry belongs to. Then you can use another sheet to report on that data. You only need one report sheet, not 12. You can use a pivot table, for example, to report on your data and use a date filter to show the data for a particular month.

That is way easier than trying to come up with complicated formulas to compensate for bad data layout, and it is a snap to sum all data for a particular category.

Answer 2

You can try in to do vlookup in one separate sheet by:

1. make list of all accounts; Comparison of accounts in Excel windows 7 between two different sheets and combining in one list

2. vlookup each sheet the specific expenses:

   • write formula, forexample:vlookup:(Sheet1_January;write in which column the amount is;false-in case such costs are not in January) - that way you'll get how much you spent on particular cost say - gas - $20;

   • at the end of the formula add "+"and do the same vlookup with next month (say, February)..and so on, adding and calculating costs for the months you want - if you have reached already say, Oct but want costs as at May, deduct in vlookup formula till May