Category totals by month

Question

Level 2

190 points

Category totals by month

I am working on my personal budget spreadsheet. I think a picture might be the simplest way to show what I am trying to do, so I made a sample sheet to demonstrate (so I didn't need to post my private info.) I am trying to create a formula for the red squares on the right to make them show what I want them to, which is the total for a category (such as groceries), for each individual month, and across multiple tables for my different accounts. Is this possible, and if so how? I've been trying, but I'm new to Numbers and this is way beyond me. Any advice is much appreciated.

Posted on Jul 6, 2023 11:29 PM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,987 points

Jul 7, 2023 12:59 AM in response to CharityJF

Hi Charity,

You screen shot appears to show Categories. Here we go back to the raw data tables.

Add another column to each account table to calculate the month name.

Formula in Current account E2: MONTHNAME(MONTH(A2))

Fill down.

Repeat with the Credit card table and any other accounts.

The SUMIFS function is your friend.

SUMIFS(sum-values, test-values, condition, test-values…, condition…)

Formula in Totals C2:

SUMIFS(Current account::D,Current account::E,A2,Current account::C,B2)+SUMIFS(Credit card::D,Credit card::E,A2,Credit card::C,B2)

Groceries in January 130, Groceries in February 185.

Regards,

Ian.

Reply

Answer 2

CharityJF Author

Level 2

190 points

Jul 11, 2023 9:54 PM in response to Yellowbox

Hi again,

I've been trying to apply this formula to the real table I have (as opposed the examples we discussed) and unfortunately I just can't get the formula to work. My real table is laid out a bit differently & is more complicated & no matter what I do I can't make the formula fit.

This is my overview of my outgoings for the year, with header columns for figures for the whole year on the left in the black box, then broken down into months on the right. 'Allocation' means 'Budget' - I just prefer that word. I'm trying to get a formula for the circled cells, by adding together all the transactions in other tables for my current account, credit card etc. for each category (such as 'Groceries & toiletries here) for that month. This 'Outgoings' table doesn't have categories switched on. The 'General' row might look a bit like it, but it's just a normal row I've made a bit darker to act as a heading.

My transactions tables further down, DO have categories switched on to group transactions by month. Column A is usually hidden, which is why I made it white to remind me to hide it. I just added it as suggested before in order to allow me to use categories. This one is for my current account (with personal info blacked out). I also have a credit card I want to fetch values from. I'm ok with how to get values from the second table, I know 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.

Any idea what the formula should be?

Reply

Answer 3

CharityJF Author

Level 2

190 points

Jul 7, 2023 12:27 PM in response to Yellowbox

That worked beautifully, thank-you Ian! 😊

Reply

Category totals by month

Similar questions