Forum Question: SUMIFS function in Numbers

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

Comments: 8 Responses to “SUMIFS function in Numbers”

    8/13/11 @ 7:10 am

    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.

      Monica
      8/13/11 @ 10:38 am

      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.

        8/13/11 @ 11:16 am

        OK. So what about my solution. Does that work for you?

          Monica
          8/13/11 @ 11:30 am

          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.

            Monica
            8/13/11 @ 12:17 pm

            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

    Judie
    2/13/13 @ 1:44 pm

    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.

      Judie
      2/13/13 @ 1:47 pm

      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?

        2/13/13 @ 1:58 pm

        Sorry, it is hard to follow complex spreadsheets with just a description. I don’t think using an & in the formula will work, though.

Comments Closed.