Using the checkbook register that already comes with Numbers, instead of the lump sum of all the catergories up on top, I want to break down the expenditures by the months, on another worksheet, using the SUMIFS function in Numbers to see if/if not I have made my budget.
I currently have tried using the below formula with no success (I have only gotten the upside-down red triangle)
=SUMIFS(Debits,Category$,A3,Transactions :: $B2,>=,D1)
D1 is the header for the month of March, b2 are the checkbook register transaction columns, a3 is one of the expenditure categories that I want summed up for the month of March
—–
Monica
Maybe we aren't looking at the same thing. I see a "Checking Register" template, but no "checkbook register." Same thing? And I see the columns: The, Date, Description, Category, Amount, Balance. No "Debits" column.
When I use the formula =SUMIFS(Transactions :: Amount,Category,"Home") I get a sum of all of the numbers in the transactions column that have "Home" in the Category column.
Gary,
Yes, it is the Checking Register and the Amount columns. It already sums all of the numbers in the transaction column; I wanted a formula to sum all of the categories each month.
OK. So what about my solution. Does that work for you?
It sums up that category, but what additional formula is needed to sum up that category monthly instead of the lump sum? I have a test spreadsheet that I can share with you, if needed.
I got it!! It is probably the long way to go about doing it, but it works!
Here is the formula:
=SUMIFS(Transactions :: C3:C14,Transactions :: A3:A14,">=10/1/09",Transactions :: A3:A14,"<=10/31/09",Transactions :: B3:B14,"=Food")
C3:C14 is the amount range
A3:A14 is the date range
B3:B14 is the category range
So, on one table I have rows for date, where, category, and amount. On another table I have a row for weeks, Walmart, Costco, other, total. I want the second table to refer to the first table to add all purchases made at a particular store in a particular week. I came up with SUMIFS(amount, category, groceries, where, Walmart). The syntax may be wrong, but it works on my spreadsheet. I can't figure out how to get the dates to work. I tried adding "date, >02/17/2013 & <02/24/2013”, didn't work.
I'm sure there's a better way to do this, but I have no clue. I though maybe changing it all to WEEKNUM functions instead of date functions would help. So then instead of doing a date I can just try to match WEEKNUMs. Does that make any sense?
Sorry, it is hard to follow complex spreadsheets with just a description. I don't think using an & in the formula will work, though.