Formula for totaling $ Amount per Category per Month

Question

Level 1

8 points

Formula for totaling $ Amount per Category per Month

I'm doing a Numbers spreadsheet similar to a check register and one page is a budget where I'm trying to keep track of how much money was spent on each category compared to a set amount I'm trying to budget for. I have a separate table for each month of the year. I know how to make it work with a fixed range of cells, but the problem with that is its too labor intensive having to go back and forth and change those at the end and beginning of each month.

My current formula that works is: =−SUMIF(Personal::Transactions::D407:D485,D1,Personal::Transactions::E407:E485)

I have the minus SUMIF because I enter debits as negative numbers and credits as positive numbers. D407:D485 is the arbitrary range I've set it at for now, at the end of the month I'll shorten that.

If someone could guide me to a formula that would allow me to accomplish the same thing but fixed at, say, for July, where it would total all $ Amounts where the date is greater than or equal to July 1, 2014 and less than or equal to July 31, 2014, with the category of Groceries, that is what I'm looking for.

Any help would be appreciated.

Thanks!

MacBook Air, OS X Mavericks (10.9.2)

Posted on Jul 26, 2014 5:56 PM

Reply

Answer 1

SGIII

Level 8

37,562 points

Jul 26, 2014 8:15 PM in response to iQuestion101

You can add a month column to your Transactions table (using month numbers 1 through 12 , either manually input or calculated from a Date column using the MONTH function, usually is the easiest) then use SUMIFS instead of SUMIF.

Also, if you haven't already, check out the Personal Budget template. It may give you some ideas.

SG

Reply

Answer 2

iQuestion101 Author

Level 1

8 points

Jul 26, 2014 8:21 PM in response to SGIII

Thank you for your reply, I really do appreciate it.

What would my new formula be?

=−SUMIF(Personal::Transactions::D407:D485,D1,Personal::Transactions::E407:E485)

Personal Transactions D404-D485 is the category column

D1 is the cell the category name is held in the summary page, in this case "Groceries"

Personal Transactions E407-E485 is the amount column

Thanks!

Reply

Answer 3

SGIII

Level 8

37,562 points

Jul 26, 2014 8:36 PM in response to iQuestion101

See the description of SUMIFS (not SUMIF) here.

In general, instead of using a range referring to part of a column, it's more efficient in Numbers to break things up into discrete tables and refer to the whole column.

Again, recommend looking at the template to get an idea of best practices.

SG

Reply

Answer 4

iQuestion101 Author

Level 1

8 points

Jul 27, 2014 1:08 PM in response to SGIII

Thank you again for your time. I am doing now exactly what the Personal Budget template is doing (below)

What I want to do and what I'm seeking help with is adjusting my formula to break out the amounts per category, per month, while still in one long column. There must be a way. I do NOT want to break this into 12 separate tables.

Again, I appreciate your input and am still looking for help if anyone has any input. Thank you!

Reply

Answer 5

SGIII

Level 8

37,562 points

Jul 27, 2014 2:25 PM in response to iQuestion101

Have you removed extraneous rows so you can refer to a whole column (as in the template), then added a month column, and tried SUMIFS?

SG

Reply

Answer 6

iQuestion101 Author

Level 1

8 points

Jul 27, 2014 2:40 PM in response to SGIII

My spreadsheet is the exact same concept as the Personal Budget template, which when opened says "Monthly Budget". The difference being I don't want a spreadsheet for every month, I want one spreadsheet for an entire year, then I have a Budget tab within the spreadsheet for planning purposes. With one spreadsheet for the entire year, I just want to adjust my formula to be able to search the entire table, exclude all data not within the parameters of a given month, July for instance, should only include data where the Date field is >= July 1, 2014 and <= July 31, 2014, and then from there broken into the categories entered. All I'm looking for is the specific language to put in my formula to make it hunt down only data that meets those parameters, rather than having to manually go in and select the cells that make up the month. The laborious part of that is you have to set an arbitrary number of cells as a guestimate up front, then at the end of the month you have to adjust it. I'd rather automate the formula if possible, I just don't know how.

If you look at the table on the template (below), what I want to do differently than this, is have the formulas in the Actual column, pull for only a specific month. Then, I essentially have 12 of these tables on one spreadsheet tab to summarize each month's budget, actual, and difference numbers.

Sorry for my confusion. I really do appreciate your time and effort.

Reply

Answer 7

SGIII

Level 8

37,562 points

Jul 27, 2014 2:47 PM in response to iQuestion101

Have you tried these steps, as suggested above?

Add a month column to your Transactions table and populate it, either manually or with the MONTH function.
Where you now have SUMIF in the Summary table, change the function to SUMIFS and to your original column-condition pair for the category add a new column-condition pair referring to the new month column.

SG

Reply

Answer 8

iQuestion101 Author

Level 1

8 points

Jul 27, 2014 2:55 PM in response to SGIII

I just put the MONTH formula in. That is working, thank you. Now how specifically should my formula would be re-written to encompass that?

Reply

Answer 9

iQuestion101 Author

Level 1

8 points

Jul 27, 2014 3:04 PM in response to iQuestion101

I tried the following but I get an error message saying that SUMIFS requires that all range arguments are the same size. Can you show me how to write this properly and where I went wrong? Thanks!

Reply

Answer 10

SGIII

Level 8

37,562 points

Jul 27, 2014 3:13 PM in response to iQuestion101

To troubleshoot, refer to the SUMIFS entry in Formulas and Functions help (link upthread).

The basic structure is this:

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

So the column you are summing (in your case AMOUNT) is the first argument, followed by your column-condition pairs. One pair would be for category, the other for month. Since you probably will want to change the month, hard-coding it into the formula is not ideal. Perhaps you could put the month condition in a cell, either A1 of your Summary table after adding a Header Row above the current one, or even in a separate table just for entering conditions, and have your SUMIFS formula refer to that cell.

SG

Reply

Answer 11

iQuestion101 Author

Level 1

8 points

Jul 27, 2014 3:19 PM in response to SGIII

YESSSSSSS!!!!!!!! So excited! It worked! THANK YOU!!!! I appreciate your patience and help. This is going to make things so smooth! I greatly appreciate it.

What ended up working was just moving the AMOUNT information from where it was to the first argument.

Since I have a separate table for each month, hard coding it works just fine. End of each year I delete all data out and save it with the new year so all the formulas stay put.

THANK YOU!

Reply