Summing expenses between worksheets

[JLSKansas]

My spreadsheet is called “Monthly Expenses.”

The first worksheet (tab) is Expense Summary. Expense categories are listed in Column A. Columns B through M are the months. Each cell contains the total monthly expenses for each household expense category.

The second worksheet is Expense Journal. The relevant columns are, in order, Date, Category, Amount. I enter each expense in the journal every month. I then sum each amount in the appropriate category for each month and then manually enter that amount in the corresponding cell in the Expense Summary.

I’m not sure if it’s important to know this, but the dates in the Journal are written in Excel date format, but the category names are just written as text.

I would like to write a formula in each cell in the Expense Summary wherein the amounts I enter in an ongoing basis in the Expense Journal are automatically summed in the correct cell in the Summary.

I would prefer not to use a pivot table to do this.

Any help is much appreciated!

[Aardigspook]

Welcome to the forum.

This should be possible with SUMIFS or similar, but it will be much easier if you could attach a sample workbook.
Make sure there is enough data to demonstrate your need but not so much that the file is too big (if you have 10,000 rows of data, you don't need to leave them all in, for example). Include before and after sheets in the workbook if needed to show the process you're trying to complete or automate. Make sure you include the answers you want to get - put examples in, with notes if necessary to explain details.
Remember to remove any confidential information or, if a cell needs data in it to work, overwrite it with made-up data.
The paperclip icon doesn't work at the moment, so click on Go Advanced under the reply box and then scroll down to Manage Attachments to attach a file.

[JLSKansas]

Thank you, Aardigspook. I am attaching a sample workbook. I hope it's clear. I have included some explanatory text in a text box on the "After" Expense Summary worksheet.

[kersplash]

You can use a pivot table as in the attached to create your summary.

[JLSKansas]

Thanks, kersplash.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JLSKansas]

Thanks, AliGW. As I said in my original post, I'd prefer to use a formula and not a pivot table, as suggested by kersplash. I am waiting for a reply from Aardigspook, or anyone else who can help, after they have reviewed the sample spreadsheet I had uploaded.

[Aardigspook]

Here are a few options.

For all options
I've used a range of 2:300 for your expenses data in the formulae below (e.g. 'Expense Journal'!$C$2:$C$300). You should change this to as large a number as you think you'll need - the formula won't slow down appreciably even if you make it 2:10000.
Alternatively you could change the data to an Excel Table, which 'self-expands'. How to do that is easy to find with a quick web-search, if you don't know already.

Option 1
This doesn't change anything in your expenses summary except putting formulae in. Put this in B2 and drag down/across:

Please Login or Register  to view this content.

Option 2
1. First, put this in B1:

Please Login or Register  to view this content.

This makes B1 the 1st of Jan of the year in A1 - it relies on the last four characters in A1 being the year.

2. Then put this in C1 and drag along to M1:

Please Login or Register  to view this content.

This gives a date for each month in subsequent cells along the row.

3. Format B1:M1 with custom format mmm.
(You could do this before steps 1 & 2 if you want.)

4. Now put this in B2, drag down and across:

Please Login or Register  to view this content.

The slight advantage of this method over Option 1 is that it's comparing dates with dates instead of dates with text - but other than that, it's the same and probably not worth the hassle.

Option 3
1. Do the same as for Option 2 in cells B1 and C1:M1, including formatting as mmm.
2. Now put this in B2, drag down and across:

Please Login or Register  to view this content.

The advantage of this is that you can continue your Expenses Journal into subsequent years without having to change the range of the formula. If you copy the sheet then the only thing you'll need to change for 2019 is cell A1. Alternatively you could copy the table to below the 2018 table; you'd then have two things to change: the cell with the year and the two references to B$1, which would have to be changed to the correct row.


I've attached a file with all three options working. There is a fourth option of adding helper columns to the original data to calculate the month/year there, which would make the formulae shorter, but wouldn't add any extra functionality to them. They would make it easier to construct a pivot table, but you said you didn't want to use that.

Hope that helps - and isn't too confusing!

[JLSKansas]

Thank you so much, Aardigspook. This is brilliant! And thanks for all the work you put into this, or at least it looks like a lot of work to me. So glad I found excelforum.

[Aardigspook]

You're welcome. Glad we could be of help and thanks for marking the thread as Solved. We look forward to seeing you again.