Show category totals on a monthly basis

Hi,

I’m using excel 2007.

I want to manipulate my downloaded bank statements and show them in a nice spreadsheet I downloaded that sums up each expense on a monthly basis.

I have the following

Column A shows the Date
Column B shows the item (expense/income)
Column C shows the amount

I would like to sum up each item (I’ll allocate them to a category manually if I have to) per month and then show that value in another sheet (the one I downloaded).

Thanks a lot

I've got a spreadsheet to start with. I have found some formulae for adding months date ranges, but I need to break them down into categories in each month and add that total to the Budget sheet.

I can then project my expenses into the future as well.

Thanks again

Stil unclear as to what you are looking for, but it seems maybe a sumif or sumifs formula may be what you are looking for. Depending on the number of criteria will determine which formula you will need to use.

Sorry about that.

In the spreadsheet attached it shows Sheet 1 with some example entries.

They are my incoming and outgoing transactions. I manually go through them and categorise each transaction and want to total each category up on a monthly basis.

I would then like each total per month to appear in the relevant cell on the Budget sheet.

I hope this is clearer.

Thanks

Silver,

See attached. I think this may be along the lines of what you are looking for. I have added an additional column to your Sheet 1 that will return the 3 letter month abbreviation for the date in Column A. It may be beneficial to classify your incomes and expenses based on the titles on the Budget Sheet which would allow the formula to use the header for each row as the variable rather than just the "salary" being entered in the formula.

That was great zmster203. I really, really appreciate your help.

Thank you.

zmster203 Could you explain how the formulas work please?

I can't see how it picks up the months, for example. I want to change the month order.

Thanks

Hey Silver,

I have adjusted formula and have attached updated spreadsheet. Sorry the original formula in B13 was incorrect, but the other formulas were correct. I have actually removed the row numbers from the formula where necessary to capture all entries when additional lines are added on Sheet1.

With a sumifs formula, the first criteria will be the range you want to sum, or add together if all entered critiera are met. In this case, we want to sum the numbers from Column D on Sheet1 when the income/expense type equals that from Column A on the Budget tab and the month of the income/expense type equals the listed month from the Budget tab too.

After the range to sum is entered, we then enter the criteria range followed by the criteria. In this case, the first criteria is going to be the income/expense type. The criteria range is Column F, with the criteria being the particular row header. I have changed anything with a Salary income type on Sheet1 to show as Wages & Tips. By doing so allows us to enter A13 in the formula rather than "salary". It will make the formula easily expandable to multiple rows without having to adjust the row header we are trying to use as criteria.

The last criteria we are needing is the month. In this case, I have added a formula in Column G on Sheet1 to return the month abbreviation of the short date entered in Column A. By adding this formula, we can then easily reference what we are needing. Criteria range for this is Column G from Sheet1 with the criteria being row 10 for each month. The way the cells are locked for both the month B$10 and income/expense type $A13 allows us to drag the formula easily without messing anything up.

Hope this makes sense.

That helps, thanks.

I seemed to have seen the error in the previous sheet as the figures when I transfer to the new one total the same.

I had changed the rows to 2000 to accommodate more transactions, but your new one answers that.

Thanks again :-)

Hi zmster2033,

I converted everything to the new spreadsheet you sent.

However, I've noticed that the Monthly Average in column O calculates the average based on 12 months. I only have January to June figures showing so it is not giving the average based on just 6 months or however many months I am using. I know this is how the original spreadsheet works, but was wondering if you could help change this?

I don't want to have to change that column each time I add a new months figures.

Thanks

Good morning,

Could you please post the file for review?

Thanks.

Here it is. Thanks

Silver,

I believe the following formula should work for column O.

=N13/month(now())

In this case the denominator would be 6 because the month is June. On July 1, the denominator will change to 7. Every January, the denominator will reset to 1 and move from there. Is this what you are looking for? My apologies if not, may not be understanding correctly.

Thanks.

I think that's it, thank you again.

I guess I wonder if that column should give an average based on the total months (i.e. January to June = 6) or whether it should only give an average based on the number of months a value has been added (i.e. March there is a value and May there is a value = 2), but I think that is personal preference.

Thanks again :-)

Silver,

If you wanted to do the other way you could do the following which would divide by the number of months where the value entered is greater than 0.

=N13/countif(B13:M13,">0")

Thanks.

Great. I'll put that in the next column then. Thanks again