MacMost Q&A Forum • View All Forum QuestionsAsk a 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”

    13 years ago

    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
      13 years ago

      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.

        13 years ago

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

          Monica
          13 years ago

          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
            13 years ago

            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
    11 years ago

    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
      11 years ago

      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?

        11 years ago

        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.