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.
Thank you for your reply, I really do appreciate it.
What would my new formula be?
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
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.
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!
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.
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.
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.
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.