I have a spreadsheet of expenses that I want to automate a summary page. Sheet1 = Summary Sheet2 = List of expenses from source A Sheet3 = List of expenses from source B Sheet4 = List of expenses from source C The expenses are sorted by date and by type (travel, food, etc). What is the formula on the Summary sheet to say ok: =SUM (from Sheet2) (within month of June 2015) (and type is "travel") =SUM (from Sheet2) (within month of June 2015) (and type is "food") =SUM (from Sheet2) (within month of June 2015) (and type is "other") =SUM (from Sheet3) (within month of May 2015) (and type is "travel") =SUM (from Sheet3) (within month of May 2015) (and type is "food") =SUM (from Sheet3) (within month of May 2015) (and type is "other") Etc... for the summary broken down by source, by month, and by expense type. Make sense?
I think you can embed IF function so you just have formula on summary page, but I would think about making it easier by adding up the categories on each sheet and then on summary page you just have have to add up like categories.
Yes that's what I'm doing now, it's all manual. By adding up each month and type individually from each source sheet, I just have to paste the final total onto the summary without any formula needed. So the problem is when I go into something from months ago and change the type of a charge... nothing is in sync... so I have to delete that whole total from the summary page and then re-calculate it and paste again... and I sometimes forget where I made a change.
I have something exactly like this bear with me as I explain. For me I used sum if, column of months equals June, and column of type of expense equals travel. Pm me your email if you want I 'll send you my sheet with expenses.
that's why I was saying put formulas on summary sheet that pull and add category totals from each sheet.
Here is my template in short so you can see the formula. I only put stuff into the transaction page (which would be where you would as well) and the second page is the summary page like you are looking for. You can make it simpler but I like the columns I have. One reason I suggest using a drop menu like I have is to avoid spelling errors. Travel will not take the sum of one put in as trvel. The summary page has the formula which basically says sum the values of column D if the transaction has a category (as chosen in transactions column E) of Cell B10 (on summary Which is Lodging on the summary page) and the month (as selected in transactions column A) is equal to cell H2 (on summary which is June). I hope this somewhat makes sense and helps. Image Unavailable, Please Login Image Unavailable, Please Login
I use the drop down only to ensure I spell it correctly. When you input hundreds and thousands of things the last thing you want is a couple of items being spelt incorrectly and not catching any criteria to sum. The formula I use references the actually columns and rows. Once I select something from the drop down it hits the formula as being "Lodging".
You could use the Excel database functions like DSUM or DCOUNT, but this requires understanding of criteria tables. Very powerful if you learn it....and sounds like what you are looking for. From the formula bar, click the "fx" button and choose Database from the category dropdown. Find a command and click "help on this function" to learn more.
Take a look at Pivot Tables in Excel. They are designed to do what you are looking for. Also all your data can be one page instead of multiple pages.
Pivot tables, or the other easy way is to do a vlookup on the category name and tell it the column where you totaled the expenses ( =vlookup(A1,sheet2!A$1$:Z$255$,26) )where A1 is the category name on the summary sheet, A1:Z255 is the array of values on the detail sheet (called sheet2 with the categories in column A) and 26 is the number of the column (Z in this case) that has the sum on the detail sheet. This way, the summary is correct even if the category order or names change, as long as the names match between the details and summary.
