How to exclude colored cells in addition equations

[wheresthericego]

Hey guys, I was wondering if there was a way to exclude colored cells from addition in a column. I did some searching which suggested VBA but couldn't find anything which made sense to me. I will attach a simple example document to help with understanding what I am trying to accomplish.

I am building a spreadsheet to keep track of my monthly spending, and debt balance. I keep track of the total amount of bills I pay each month. As I pay them, their respective line is highlighted green in the spreadsheet. I would like to create a macro/formula which would sum the remaining cells in the column which are not highlighted to display the remaining monies which need to be paid out. Hopefully this makes sense and I will keep a close eye on the thread to answer any questions. I'm not sure if this matters but I am using office 2010.

Thanks

[mikeTRON]

You cannot sum by colour without using code. However, if you know the criteria based on which the cells were coloured, you may use the SUMPRODUCT() function [which will take the criteria as input].

http://www.exceltrick.com/how_to/sum...kground-color/
http://www.cpearson.com/excel/colors.aspx
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

[mehmetcik]

This is your macro

right click on the sheet name at the bottom of excel and click on view code to edit it.

Please Login or Register  to view this content.

[wheresthericego]

Thank you for your responses! While I was not able to get it to work based on the color of a cell I used SUMIF to accomplish my goal with a date in the function criteria. I'll attach the finished table, maybe someone here will find it handy to keep track of how much their monthly expenses are, how much has been paid, and how much is remaining. Each month in the year I copy the sheet so I have a historical log. Also, I need to keep track of how much taxes I am paying compared to the prior year so there are a couple tables which calculate the percentages. Of course the numbers in the table are fictional and there to help understand what the table does.

Thanks

[mikeTRON]

Great, so can you mark this solved?