Sum cells based on color in Mac 2008

**KO3** · 10-25-2009, 12:10 AM

I am trying to SUM cells in Excel 2008 based on the cell's background color. I know there is a way to create a formula for this if you are using PC versions of Excel, but I can't figure out how to do it for Excel 2008. Do I need to use Applescript? If so, what do I need to do?

Thanks.

**JBeaucaire** · 10-25-2009, 01:00 PM

What logic was used to color the cells? Instead of trying to sum by color, sum by the same logic.

**shg** · 10-25-2009, 01:02 PM

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

**KO3** · 10-26-2009, 10:28 PM

Thanks for the response (and title change suggestion). I posted this question at Yahoo Answers at the same time I posted it here. Here's the link:

http://answers.yahoo.com/question/in...4200202AAVFi7p

JBeaucaire: In your post, you suggested trying to sum by the same logic, but I'm not sure what you meant. What I'm trying to do is sum cells by two different types of logic. I have three columns with sum totals for each column, and what I want to do is also categorize the cells from all three columns by another logic. Does that make sense? I apologize for my lack of clarity and use of proper "lingo"--I'm new to Excel and completely self-taught.

Thanks.

**JBeaucaire** · 10-26-2009, 11:05 PM

What I mean is "Why were the cells colored?" There was SOME logic behind your choice to color the cells. Humans do that...we color things.

But Excel is perfectly capable of using the same logic you used to color cells to count by. So, explain why you colored them, what the colors mean, and I'm sure something can be suggested for turning THAT logic into a sum formula.

**KO3** · 10-27-2009, 12:55 AM

Thank you for using layman's terms... I appreciate it!

I am trying to create a personal budget spreadsheet. I know there are plenty of free templates online, but I wanted to create something simple that fit my specific needs without all of the extra bells and whistles.

I want to keep track of all of my purchases. I have them all in a chronological, horizontal list. From left to right, I have the date, the amount of the transaction, and a description. I input the amount of the transaction in one of three columns: "cash," "checking," or "savings." At the top I have a formula to sum how much money was spent in each of those categories.

I also want to categorize each transaction as "food," "bills," "gas," etc. This is where I thought the colors would come in handy. Food purchases could be red cells, bills could be blue, etc. At the top, I would like to have another formula that can sum the amount from all of the red cells, whether they are from the "cash" or "checking" or "savings" category, so I can see how much was spent on food (and all of the other color categories).

I learned from reading other forums that previous versions of Excel just need a macro to create a new formula, but for whatever reason I can't do that in Excel 2008. Is there a way to create a new formula that can sum cells based on color? Or is there another, color-less solution?

Thanks.

**JBeaucaire** · 10-27-2009, 03:25 AM

Create a new column in your sheet called CATEGORY. Instead of placing a value in the CASH column and coloring it BLUE for "food", just put the word "Food" in the new column on the same row.

Also, separate columns for types of spending is non-standard, too. Let another column be the TYPE column. Now each row of data uses the same # of columns and still has all the pertinent info for you to do simple SUMIF() formulas on the data.

Code:

     A		 B	  C	    D		E	  F	  G		H	I
1  Date	      Category	Type	  Amount		SUMMARY			
2  10/1/2009	Food	Cash	  $100.00 		Food	 $145.00    Cash      $125.00 
3  10/2/2009	Bills	Checking  $100.00 		Bills	 $100.00    Checking  $150.00 
4  10/3/2009	Food	Cash	  $25.00 		Gas	 $30.00     Savings   $  0.00   
5  10/4/2009	Gas	Checking  $30.00 					
6  10/5/2009	Food	Checking  $20.00

G2:
=SUMIF(B:B,$F2,$D:$D)

I2:
=SUMIF(C:C,$H2,$D:$D)

Just copy those formulas down for all your CATEGORIES and TYPES.

**KO3** · 10-27-2009, 09:29 AM

Wow, that makes so much more sense. I never knew how the SUMIF formula worked. Thank you so much for such a thorough response. This has been my first "forum" experience, and I can't believe how simple it was to get help quickly and easily.

Thank you again!