**Paul Applegate1**Aug 12, 2007 11:38 AM

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.

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)

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.

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.

**See the answer in context**