6 Replies Latest reply: Aug 12, 2007 2:04 PM by Twoeoverh
Paul Applegate1 Level 1 (105 points)
I have a simple bank balance spreadsheet.
The headers include the following:
Date Amount Category Cleared Balance

The category is a pop-up list, cleared is a check list

What formula do I use to add the total amount spent in a category, for the purpose of making a spending graph? I guess I need to "look up" the "kind" in the column and then get a sum of the amount, but I am not sure how to do this. I am sure it is simple.

MacBook Pro Intel Core 2 Duo, Mac OS X (10.4.10)
  • Cii Level 1 (0 points)
    One of the templates in Numbers is called Personal Budget. That should have what you are looking for interms of selecting from categories. (the sumif function)
  • Twoeoverh Level 1 (10 points)
    This method may not be particularly elegant but I have been using it successfully for years.

    Okay, let's assume for this example that you have Columns A through E with the headers you specified, Date, Amount, Category, Cleared, and Balance. Further, you have a pop-up menu on the cells of the Category menu. Again for this example let's assume the pop-up values are "Utilities", "Food", "Clothing", and "Other". Now you need to establish 4 columns (one for each of the menu items). Let's use columns G through J. In the header of column G, type Utilities. In the header of column H, type Food, and so forth.

    Now in cell G2 enter the following formula =IF($C2=G$1,$B2,)
    Copy the formula into cells H2 through J2 (fill right) and then copy the formula from cells G2 through J2 (fill down) down the spreadsheet to as many rows as you think you will have entries.

    What this formula does is examine the contents of the Category cell and compares it to the text in the header of the column. If they match the $ contents of Amount are copied to the new cell. So, when you get done with this you have 4 columns of mostly zeros except where the categories match. Then all you have to do is at the bottom of each of the 4 columns (possibly in the footer), sum the column and you will have the individual sums for each category.

    I hope this wasn't too confusing. There may be a more elegant way and if someone knows it maybe they will post it and I'll learn too.
  • Paul Applegate1 Level 1 (105 points)
    The personal budget has categories, but they are not generated from a pop-up list, nor do the sum any items, they just list a manually inputted sum.
  • Paul Applegate1 Level 1 (105 points)
    I will try this path of thinking. Thanks.
  • Paul Applegate1 Level 1 (105 points)
    Thanks, that worked.
  • Twoeoverh Level 1 (10 points)
    The previous answer suggesting SUMIF is a good one. For the example I gave before all you have to do is enter the following formula in a cell somewhere, =SUMIF(C2:C100,"=Utilities",B2:B100). This would do a sum of the rows that match Utilities (from 2 to 100 obviously). The only problem with this method is that you have to type in a separate formula for each category (no filling) but more importantly if you go past row 100 when filling in information you have to remember to change the formula, which is the kind of thing that leads to spreadsheet errors. I always create a checksum by summing the sums and comparing that to the grand total.

    I haven't found a way yet to substitute a condition that compares to a header cell, i.e., =G$1 instead of =Utilities.