Any Excel experts?

firsttimebuyer2013

I'm trying to do a 'basic' spending diary/budget tracker.

I've got a sheet with each month along the top, and categories such as mortgage, gas, groceries down the left. They all add up over the year to give totals and averages. All working, all good.

However, I also want to add the spending diary on other sheets for each month.

It will consist of along the top, date/payment type/item/category/price. I've got drop down menus on Payment type and category. The options under category include mortgage/gas/groceries etc. What I want it to do is when I put say groceries in for £50 I want that to add £50 to the first sheet on the groceries line. Then when I add another £25, for it to add it to the £50 already.

If you've managed to understand what on earth I'm talking about, is this even possible? :undecided

ThemeOne

Have you looked at the SUMIF function?

firsttimebuyer2013

Is that suitable? I'm not very good at Excel, can just about work out SUM with the help of Google. :undecided

ThemeOne

From your post, it might be worth a try. Basically it adds numbers from a range if a cell in another range holds a specified value.

For example you could set it up so it will add the figures in your Price column but only if the price relates to Groceries.

The Excel help usually includes some examples, so maybe take a look at those for SUMIF.

Mr_Toad

Yes it is definitely possible. When using more than one sheet in your formulas you need identify the sheet as well as the cells so to use cell B3 on sheet 2 in a calculation on sheet 1 you would need to use sheet2!B3

Have a look on YouTube, there are some excellent tutorials on there that should help. There are loads, this the first one I found.

https://www.youtube.com/watch?v=gjmvpwPGzhQ

firsttimebuyer2013

Thanks. I've got the calculation bit working, from one sheet to another, I just now need to make it work for the different drop down menu options. I've tried googling, but I'm not entirely sure how to word what I'm after.

So for example;
http://imgur.com/a/qYgIG

ThemeOne

So you would do your SUMIF formula on the Overview sheet in cell B15 for Groceries.

In the formula you would reference the figures in the Price column in the January sheet, and also the value in the Category column in the January sheet.

It would look something like
=SUMIF(D2: D100,"Groceries",E2:E100)

where 100 is the last row you have used on the sheet.

Your cell references will look a bit different as you need reference a different worksheet.

zenmaster

I have a similar spreadsheet which I use to track my income and expenditure from my downloadable bank statements. I will try to simplify it for you.

On the front sheet which is, imaginatively, called "Front Sheet" I have the following:
Cells B1 .. M1 contain the headings "Jan" .. "Dec"
Cells A2 .. Ax contain the categories, as many as you need.

I'll come back to this in a moment.

I then have 12 other sheets called "Jan" .. "Dec". Each of these contains the following:

Row A contains headers taken from the bank statement -
Date, Type, Description, Paid Out, Paid In and Balance. To these I have added 2 more - Category and Comments.

Row B onwards contains all the data pasted directly from the bank statement. For each row I assign a Category equating to one of those on the Front Sheet. The Comments field is just for expansion, e.g. if the Category is "Stationery" then I might add "Printer Ink" in the Comments field.

Having set up the sheets for the individual months (they do not need to contain any data) go back to the front sheet.

In cell B2 enter the following formula

=SUMIF(INDIRECT("'"&B$1&"'!G2:G50"),$A2,INDIRECT("'"&B$1&"'!D2: D50"))

Copy this down the list of categories and across all the months and Robert is your Father's brother.

INDIRECT("'"&B$1&"'!G2:G50") picks up the month from the heading, which indicates the required worksheet, and appends the cell range G2:G50 to it, which is where the category is allocated. 50 is an arbitrary number. If the number of your monthly transactions exceeds this you will need to increase it.

INDIRECT("'"&B$1&"'!D2: D50")) similarly picks up the month from the heading but this time refers to the "Paid Out" column.

The whole formula means "add up all the amounts in the "Paid Out" column which are associated with the category to the left".

I also found it useful to have an extra category at the bottom of the list which I call "Unaccounted". This contains the formula

=SUMIF(INDIRECT("'"&B$1&"'!G2:G50"),"",INDIRECT("'"&B$1&"'!D2: D50"))

which is similar to the main formula but checks for blanks so you can spot if you have not allocated a category to any entry.

firsttimebuyer2013

You sir, are an absolute superstar. I just had to add January! to each 'command' and it works a treat.

I'm hoping you've helped me save a lot of money next year. Thanks very much. :beer: