Is there a way to have a formula do something based on the color of a cell? For example, you have numbers in cells A1:A5000. You want the sum of those numbers so you do a simple =sum(A1:A5000). But let’s say you want to black out the numbers, but still have them be there. Is there a way to make the sum cell skip any cell that is blacked out? or multiply a cell by 2 before it uses the number in the sum for all green cells?
Do you have 2007 or 2003? In 2007 you can use data filters and filter by color
Well, I assume that cells which are blacked out have conditional formatting on them. Example - black out anything with value greater than 100 so then why not use SUMIF(A:A,">100")
No they don’t have conditional formatting on them. In the example above, let’s assume it’s a list of money owed. When an amount is paid, I want to show that it’s been paid by blacking out the cell. I want the sum cell to automatically reflect the fact that the cell has been paid and not put black cells in the calculation. I know there are many easier ways to do this, but my client is insisting I try to do something like this. I’m also curious to know if it can even be done w/out extensive VBA coding.
Why do not you create another column showing if the money has been paid or not. Use that for blacking he cell as well for summing the ones that has not been paid. Just making it black does not capture the full information anyway.
Yea, I know I can do a SUMIF with an adjacent cell, I was just curious if this method was possible.
yeah, without code, you can’t do it. See this for how to do it with conditional formatting. I tested it, it works. http://www.excel-spreadsheet-authors.com/excel-filter-by-font.html
hey, i dont think without code its possible, but there is a very simple code which will do what you require…the link of the code is at http://en.allexperts.com/q/Excel-1059/Cell-color-3.htm so once you have copied this code into your editor its easy suppose the number u have are in the cells A2 to A25, in cell B2 u have to type “=IF(cellcolor(A2,TRUE)=“black”,A2,0)+B1” drag that down, and you would get the sum of the black cells if you wish i can send u my excel sheet