11 Replies Latest reply: Jul 27, 2014 3:19 PM by iQuestion101
iQuestion101 Level 1 Level 1 (0 points)

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.

 

Screen Shot.png

 

Any help would be appreciated.

 

Thanks!


MacBook Air, OS X Mavericks (10.9.2)
  • SGIII Level 5 Level 5 (5,590 points)

    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

  • iQuestion101 Level 1 Level 1 (0 points)

    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!

  • SGIII Level 5 Level 5 (5,590 points)

    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

  • iQuestion101 Level 1 Level 1 (0 points)

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

    Screen Shot 2014-07-26 at 10.20.05 PM.png

    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!

  • SGIII Level 5 Level 5 (5,590 points)

    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

  • iQuestion101 Level 1 Level 1 (0 points)

    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.

    Screen Shot 2014-07-27 at 2.39.17 PM.png

     

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

  • SGIII Level 5 Level 5 (5,590 points)

    Have you tried these steps, as suggested above?

    1. Add a month column to your Transactions table and populate it, either manually or with the MONTH function.
    2. 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

  • iQuestion101 Level 1 Level 1 (0 points)

    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?

  • iQuestion101 Level 1 Level 1 (0 points)

    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!

    Screen Shot 2014-07-27 at 3.02.13 PM.png

  • SGIII Level 5 Level 5 (5,590 points)

    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

  • iQuestion101 Level 1 Level 1 (0 points)

    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!