Formula for monthly spend on personal budget

Question

Level 2

190 points

Formula for monthly spend on personal budget

Hi, I am working on my personal budget spreadsheet & really struggling to work out a certain formula I need. I'll explain the layout of the spreadsheet in the hope someone can help. I have an overview of the year (the red table pictured):

As you can see, I have different areas of spending on the left, e.g. groceries, clothes etc. I have headings for these such as 'General' & 'Business'. These might look a bit like Numbers 'Categories', but they aren't; they're just normal rows I made a slightly darker colour. As the picture shows, I have some 'header' columns that summarise the whole year. I then have columns for each month.

It's the monthly 'Actual' columns I'm having trouble with. I want to pull figures into this column from my transactions tables lower down, but I can't get it to work. As an example, G1 needs to be the sum of all my 'Groceries & toiletries' transactions in January of this year, from the various tables I have for my different accounts. Here's one of those transactions tables (with personal info blacked out.) This one is for my current account, others are for my credit card and a savings accounts.

In these tables I have categories switched on to group transactions by month. Column A is usually hidden. I only added it because I tried to find out how to do this before and it was suggested to add a month column to help the formula work, but that was on a simpler sample table with a different layout and I haven't been able to get that to work here.

I'm think I'm ok with how to get values from multiple transaction tables, I think I just need to use a plus sign & repeat the formula. The problem is I can't get the formula right for even just the first table.

Can anyone help please?

iMac 24″, macOS 12.6

Posted on Jul 14, 2023 10:26 PM

Reply

Answer 1

Best reply

SGIII

Level 8

35,504 points

Jul 15, 2023 5:04 PM in response to CharityJF

The way I hide zeros is to create a custom format (at the bottom of the Data Format dropdown) that looks like this (you would need to modify slightly to display currency by dragging the $ up into the box):

That is a simple way to hide visual clutter.

To distinguish between "true zeros" and sums of blank cells might introduce more complication than it's worth, though.

SG

Reply

Answer 2

CharityJF Author

Level 2

190 points

Jul 15, 2023 5:23 AM in response to Yellowbox

Thank-you Ian.

I searched for posts by him that might contain the document, and from the results managed to figure it out! This is the formula that worked:

Now I just need to tweak it, because it's showing "£0.00" wherever I don't have any transactions for that month & that category, but I find that too much distracting visual clutter. How can I make it so the cell is just blank if there are no entries for that category in that month? I don't want it to show blank if the value is genuinely zero, say if I'd had spent £100 in a certain category, but also been refunded the same amount... in that case I would want to see "£0.00". Do you know how to do that?

Reply

Answer 3

CharityJF Author

Level 2

190 points

Jul 16, 2023 12:54 PM in response to SGIII

Ah ok SG, thanks for that info, I can imagine I may still use that somewhere. I had hoped there would be a way to have the cells show as blank if there were no entries in my transaction tables for that category in that month (e.g. if there was a month I spent nothing on clothes for example.)

I'll either use your workaround (it doesn't really matter too much to see zeros if my income & outgoings are the same; and if it does I can just change that one cell.) That or I'll just delete my formula in the cells with £0.00 in them; it's easy enough to reinstate if necessary by filling down or copy & pasting.

Reply

Answer 4

Yellowbox

Level 7

20,507 points

Jul 15, 2023 12:30 AM in response to CharityJF

Hi Charity,

SGIII created a document to sum expenses by category and month. Perhaps SGIII will stop by soon.

Regards,

Ian.

Reply

Formula for monthly spend on personal budget

Similar questions